USE: To display all table constraints from a database. SET NOCOUNT ON SELECT A.name [TableName] , B.name [ColumnName] , C.name [ConstraintName] , C.[type] [ConstraintType] , A.crdate FROM sysobjects A INNER JOIN sys.columns B ON A.id = B.object_id INNER JOIN sys.key_constraints C ON A.id = C.parent_object_id WHERE A.Xtype = ‘U’ AND B.is_identity = 1 ORDER BY […]

USE: To display database detail information. SELECT @@SERVERNAME [ServerName], MF.data_space_id, MF.database_id, MF.[type], MF.[type_desc], MF.[name] [DBName], MF.[physical_name] [FilePath], MF.[state_desc], CAST((MF.[size] * 8.0) / 1024.0 AS numeric(19,2)) [FileSize (MB)], MF.[max_size], MF.[growth], DB.[create_date], DB.[compatibility_level], DB.[collation_name], DB.[user_access_desc], DB.[state_desc], DB.[recovery_model] FROM sys.master_files MF INNER JOIN sys.databases DB ON MF.database_id = DB.database_id LEFT JOIN sys.data_spaces DS ON MF.data_space_id = DS.data_space_id LEFT […]

USE: To display database file size information at the server level. SELECT @@SERVERNAME [ServerName], MF.database_id, DB.[name] [DBName], SUM(CAST((MF.[size] * 8.0) / 1024.0 AS numeric(19,2))) [Total Space Usage (MB)] FROM sys.master_files MF INNER JOIN sys.databases DB ON MF.database_id = DB.database_id GROUP BY MF.database_id, DB.[name] ORDER BY MF.database_id GO  

USE: To capture the result from executing sp_who2 system stored procedure. SET NOCOUNT ON IF OBJECT_ID(‘tempdb..#SP_WHO2’) IS NOT NULL DROP TABLE #SP_WHO2 GO CREATE TABLE #SP_WHO2 ( SPID varchar(128) , [Status] char(12) , [Login] varchar(128) , [HostName] varchar(128) , [BlkBy] varchar(128) , [DBName] varchar(128) , [Command] varchar(128) , [CPUTime] varchar(128) , [DiskIO] varchar(128) , LastBatch […]

USE: To find out recent database files growth. IF(SELECT CONVERT(int,value_in_use) FROM sys.configurations WHERE [name] = ‘default trace enabled’ ) = 1 BEGIN declare @curr_tracefilename varchar(500) ; declare @base_tracefilename varchar(500) ; declare @indx int ; SELECT @curr_tracefilename = [path] FROM sys.traces where is_default = 1 ; SET @curr_tracefilename = REVERSE(@curr_tracefilename); SELECT @indx = PATINDEX(‘%%’, @curr_tracefilename) ; […]

DECLARE @TracePath nvarchar(256) SELECT @TracePath = [path] FROM master.sys.traces SELECT * FROM ::fn_trace_gettable (@TracePath,default) SELECT T.EventID , T.ColumnID , E.Name [EventDescription] , C.Name [ColumnDescription] FROM ::fn_trace_geteventinfo(1) T INNER JOIN sys.trace_events E ON T.EventID = E.trace_event_id INNER JOIN sys.trace_columns C ON T.columnid = C.trace_column_id SELECT T.EventID , E.Name [EventDescription] , COUNT(*) [DataColumnCount] FROM ::fn_trace_geteventinfo(1) T INNER […]