PROBLEM: I need to find out all the tables in a database that are not using data compression. SOLUTION: Here is the T-SQL code to list all tables in a database that are not compressed. The script below will take care of generating the T-SQL commands necessary to compress all tables that are not already … Read more
USE: To display index information.
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 … Read more
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 … Read more
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 … Read more
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) ; … Read more