Feeds:
Posts
Comments

searchatable

USE
DB;

GO

 

ALTER
SCHEMA dbo TRANSFER
[existing_schema].[t_table_name]

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

 

 

 

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

BEGIN

 
DROP TABLE
tblname

END

GO

 

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

 

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

 

SELECT

     Restore_Date, 

     destination_database_name,

     User_Name

FROM
MSDB.dbo.RestoreHistory

ORDER BY
Restore_Date DESC

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: