MSSQL Tricks & Hints

Patching up leading zeroes
UPDATE TheTable
set FILE_NBR = replicate('0', 6-len(FILE_NBR) ) + FILE_NBR

Querying disk file sizes

use master

SELECT DB_NAME(DATABASE_ID) AS [DATABASE_NAME], name as FileName, Type_desc,
-- hmmmm 8k blocks?
CAST(( CAST(SIZE * 8 AS FLOAT) ) / 1024 AS VARCHAR) + ' MB' AS [SIZE],
CAST( (MAX_SIZE / 128) AS VARCHAR) + ' MB' AS [MAX_SIZE]
FROM SYS.MASTER_FILES


Querying table and View Names
select * from MyDataBase.INFORMATION_SCHEMA.TABLES

Getting column names
select * from MyDataBase.INFORMATION_SCHEMA.Columns

-- st proc and functions
select * from MyDataBase.INFORMATION_SCHEMA.Routines -- sp and function listing
select * from MyDataBase.INFORMATION_SCHEMA.Routine_Columns -- Columns Used In Routines & sp
select * from MyDataBase.INFORMATION_SCHEMA.Parameters -- args to st proc and functions

-- show columns that are referenced in views
select * from MyDataBase.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE