Feeds:
Posts
Comments

Archive for the ‘Database’ Category

searchatable

Read Full Post »

USE
DB;

GO

 

ALTER
SCHEMA dbo TRANSFER
[existing_schema].[t_table_name]

Read Full Post »

CREATE
TABLE Sales

(

[ProductCategoryName]
NVARCHAR(50),

[ProductName]
NVARCHAR(50),

[SalesAmount]
MONEY

)

GO

 

INSERT
Sales

SELECT
‘Bikes’,‘Mountain-100 Silver’,3399.99
UNION ALL

SELECT
‘Components’,‘Road-650 Black’,699.0982
UNION ALL

SELECT
‘Components’,‘Road-150 Red’,3578.27
UNION ALL

SELECT
‘Bikes’,‘Mountain-100 Silver’,3399.99
UNION ALL

SELECT
‘Bikes’,‘Mountain-100 Silver’,3399.99
UNION ALL

SELECT
‘Bikes’,‘Mountain-100 Black’,3374.99
UNION ALL

SELECT
‘Bikes’,‘Mountain-100 Silver’,3399.99
UNION ALL

SELECT
‘Components’,‘Road-150 Red’,3578.27
UNION ALL

SELECT
‘Components’,‘Road-150 Red’,3578.27
UNION ALL

SELECT
‘Components’,‘Road-150 Red’,3578.27
UNION ALL

SELECT
‘Components’,‘Road-150 Red’,3578.27
UNION ALL

SELECT
‘Components’,‘Road-650 Red’,699.0982

GO

 

SELECT
* FROM
Sales

GO

 

 

GROUP BY

SELECT
ProductCategoryName,ProductName,SUM(SalesAmount)AS TotalSalesAmount FROM
Sales

GROUP
BY ProductCategoryName,ProductName

 

 

WITH ROLLUP 

SELECT
ProductCategoryName,ProductName,SUM(SalesAmount)AS TotalSalesAmount FROM
Sales

GROUP
BY ProductCategoryName,ProductName

WITH
ROLLUP

 

 

Read Full Post »

 

IF
EXISTS (SELECT 1 FROM
sysobjects WHERE type = ‘U’ and name = ‘tblname’)

BEGIN

 
DROP TABLE
tblname

END

GO

 

Read Full Post »

For one table

sp_spaceused ‘TABLE_NAME’

 

For all tables

EXEC
sp_msForEachTable ‘EXEC
sp_spaceused ”?”’

 

–OR

 

CREATE
TABLE #TableSize

(

   
SEQ INT IDENTITY(1,1) ,

   
name NVARCHAR(200),

   
rows BIGINT,

   
reserved VARCHAR(100),

   
data VARCHAR(100),

   
index_size VARCHAR(100),

   
unused VARCHAR(100)

)

 

INSERT
#TableSize

EXEC
sp_msForEachTable ‘EXEC
sp_spaceused ”?”’

 

SELECT * FROM
#TableSize

Read Full Post »

 

EXEC
SP_HELPDB

 

–OR

 

CREATE
TABLE #DatabaseSize

(

            NAME VARCHAR(100),

            DB_SIZE
VARCHAR(100),

            OWNER VARCHAR(100),

            DBID INT,

            CREATED
DATETIME,

            STATUS VARCHAR(500),

            COMPATIBILITY_LEVEL
INT

)

 

INSERT
INTO #DatabaseSize

EXEC
SP_HELPDB

 

SELECT
NAME,DB_SIZE
FROM #DatabaseSize

 

Read Full Post »

SELECT

     Restore_Date, 

     destination_database_name,

     User_Name

FROM
MSDB.dbo.RestoreHistory

ORDER BY
Restore_Date DESC

Read Full Post »

 

SELECT ss.last_run_outcome /*1-Success,0-Fail*/

 

FROM msdb.dbo.sysjobs s INNER JOIN msdb.dbo.sysjobservers ss

 

ON s.job_id = ss.job_id

 

WHERE s.name= ‘Job_Name’

 

Read Full Post »

CREATE
TABLE  tbl

(

ID 
INT IDENTITY(1,1),

NAME
VARCHAR(100)

)

GO

 

INSERT
tbl

SELECT
‘NAME1’

UNION ALL

SELECT
‘NAME1’

UNION ALL

SELECT
‘NAME2’

GO

 

 

WITH
CTE_Duplicate

AS

(

SELECT
Rank=ROW_NUMBER() OVER ( PARTITION BY
NAME ORDER
BY NAME),*

FROM
tbl

)

 

DELETE
FROM CTE_Duplicate

WHERE
Rank>1

GO

 

 

 

 

Read Full Post »

 

–Create table

CREATE
TABLE tbl

(

 Product_Name
VARCHAR(100),

 QTY
INT

)

GO

 

–Insert rows

INSERT
tbl

SELECT
‘prd1’,10

UNION ALL

SELECT
‘prd2’,20

UNION ALL

SELECT
‘prd3’,30

UNION ALL

SELECT
‘prd1’,40

UNION ALL

SELECT
‘prd2’,50

GO

 

SELECT
* FROM 
tbl

GO

 

 

–Dynamic Pivot

DECLARE
@PivotColumn VARCHAR(MAX)

SELECT
@PivotColumn =

                                                COALESCE(

                                                @PivotColumn
+ ‘,[‘ + cast(Product_Name as
varchar)
+ ‘]’,

                                                ‘[‘ +

                                                cast(Product_Name as varchar)+ ‘]’

                                                )

                                                FROM (

                                                                        SELECT DISTINCT
Product_Name FROM tbl)b

 

DECLARE
@PivotSQL NVARCHAR(MAX)

SET
@PivotSQL = N

                                                SELECT *

                                                FROM (

                                                SELECT

                                                Product_Name,QTY
from tbl

                                                ) AS

                                                PivotData

                                                PIVOT (

                                                SUM(QTY)

                                                FOR Product_Name

                                                IN (

                                                ‘ +

                                                @PivotColumn
+

                                                )

                                                ) AS

                                                PivotTable’

 

EXECUTE(@PivotSQL)

GO

 

 

Read Full Post »

Older Posts »

%d bloggers like this: