Dear Readers,
I am back with part 4 of Cheatcode series after gap of almost one month. In First three Parts of this series I covered, Few Generic Scripts, Replication Specific Scripts & Database Backup\Restore Specific Scripts.
In this Series, I try to cover those Scripts which we can use in our day to day operations as a DBA. So here is the forth Installment of the Series. Keys and Index Special!!!
Comments are most welcome by readers which can also enhance my database of queries.
**DISCLAIMER : I have personally used these queries but they may or may not work in your environment depending on your server setting, server edition\version etc. Please test these queries in lower environment first before executing them in production directly**
Note :- I would like to thank countless sites and articles present there on google which helped me save all these queries to my personal QUERY DATABASE. This series is for knowledge sharing pupose only and you can share these queries again to your colleagues and friends.
QUERY 1 : Script to Generate Foreign Key Creation Script for all tables in a Database :-
This Script will script out all foreign keys present across all tables in a Database. You can edit this script to get the script of foreign keys for a specific table of few random tables as well.
--- SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS declare @ForeignKeyID int declare @ForeignKeyName varchar(4000) declare @ParentTableName varchar(4000) declare @ParentColumn varchar(4000) declare @ReferencedTable varchar(4000) declare @ReferencedColumn varchar(4000) declare @StrParentColumn varchar(max) declare @StrReferencedColumn varchar(max) declare @ParentTableSchema varchar(4000) declare @ReferencedTableSchema varchar(4000) declare @TSQLCreationFK varchar(max) --Written by Percy Reyes www.percyreyes.com declare CursorFK cursor for select object_id--, name, object_name( parent_object_id) from sys.foreign_keys open CursorFK fetch next from CursorFK into @ForeignKeyID while (@@FETCH_STATUS=0) begin set @StrParentColumn='' set @StrReferencedColumn='' declare CursorFKDetails cursor for select fk.name ForeignKeyName, schema_name(t1.schema_id) ParentTableSchema, object_name(fkc.parent_object_id) ParentTable, c1.name ParentColumn,schema_name(t2.schema_id) ReferencedTableSchema, object_name(fkc.referenced_object_id) ReferencedTable,c2.name ReferencedColumn from --sys.tables t inner join sys.foreign_keys fk inner join sys.foreign_key_columns fkc on fk.object_id=fkc.constraint_object_id inner join sys.columns c1 on c1.object_id=fkc.parent_object_id and c1.column_id=fkc.parent_column_id inner join sys.columns c2 on c2.object_id=fkc.referenced_object_id and c2.column_id=fkc.referenced_column_id inner join sys.tables t1 on t1.object_id=fkc.parent_object_id inner join sys.tables t2 on t2.object_id=fkc.referenced_object_id where fk.object_id=@ForeignKeyID open CursorFKDetails fetch next from CursorFKDetails into @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn while (@@FETCH_STATUS=0) begin set @StrParentColumn=@StrParentColumn + ', ' + quotename(@ParentColumn) set @StrReferencedColumn=@StrReferencedColumn + ', ' + quotename(@ReferencedColumn) fetch next from CursorFKDetails into @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn end close CursorFKDetails deallocate CursorFKDetails set @StrParentColumn=substring(@StrParentColumn,2,len(@StrParentColumn)-1) set @StrReferencedColumn=substring(@StrReferencedColumn,2,len(@StrReferencedColumn)-1) set @TSQLCreationFK='ALTER TABLE '+quotename(@ParentTableSchema)+'.'+quotename(@ParentTableName)+' WITH CHECK ADD CONSTRAINT '+quotename(@ForeignKeyName) + ' FOREIGN KEY('+ltrim(@StrParentColumn)+') '+ char(13) +'REFERENCES '+quotename(@ReferencedTableSchema)+'.'+quotename(@ReferencedTable)+' ('+ltrim(@StrReferencedColumn)+') ' + char(13)+'GO' print @TSQLCreationFK fetch next from CursorFK into @ForeignKeyID end close CursorFK deallocate CursorFK
QUERY2 :- Script to generate Create script of all Primary Keys and Constraint across all tables in a Database :-
Below Script can be used in same way as the previous Script but this generate Scripts of all Primary or Unique Keys and constraints only.
--- SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS. declare @SchemaName varchar(100) declare @TableName varchar(256) declare @IndexName varchar(256) declare @ColumnName varchar(100) declare @is_unique_constraint varchar(100) declare @IndexTypeDesc varchar(100) declare @FileGroupName varchar(100) declare @is_disabled varchar(100) declare @IndexOptions varchar(max) declare @IndexColumnId int declare @IsDescendingKey int declare @IsIncludedColumn int declare @TSQLScripCreationIndex varchar(max) declare @TSQLScripDisableIndex varchar(max) declare @is_primary_key varchar(100) declare CursorIndex cursor for select schema_name(t.schema_id) [schema_name], t.name, ix.name, case when ix.is_unique_constraint = 1 then ' UNIQUE ' else '' END ,case when ix.is_primary_key = 1 then ' PRIMARY KEY ' else '' END , ix.type_desc, case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end + case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end + case when ix.allow_row_locks=1 then 'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end + case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end + case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions , FILEGROUP_NAME(ix.data_space_id) FileGroupName from sys.tables t inner join sys.indexes ix on t.object_id=ix.object_id where ix.type>0 and (ix.is_primary_key=1 or ix.is_unique_constraint=1) --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName and t.is_ms_shipped=0 and t.name<>'sysdiagrams' order by schema_name(t.schema_id), t.name, ix.name open CursorIndex fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName while (@@fetch_status=0) begin declare @IndexColumns varchar(max) declare @IncludedColumns varchar(max) set @IndexColumns='' set @IncludedColumns='' declare CursorIndexColumn cursor for select col.name, ixc.is_descending_key, ixc.is_included_column from sys.tables tb inner join sys.indexes ix on tb.object_id=ix.object_id inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id inner join sys.columns col on ixc.object_id =col.object_id and ixc.column_id=col.column_id where ix.type>0 and (ix.is_primary_key=1 or ix.is_unique_constraint=1) and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName order by ixc.index_column_id open CursorIndexColumn fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn while (@@fetch_status=0) begin if @IsIncludedColumn=0 set @IndexColumns=@IndexColumns + @ColumnName + case when @IsDescendingKey=1 then ' DESC, ' else ' ASC, ' end else set @IncludedColumns=@IncludedColumns + @ColumnName +', ' fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn end close CursorIndexColumn deallocate CursorIndexColumn set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1) set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end -- print @IndexColumns -- print @IncludedColumns set @TSQLScripCreationIndex ='' set @TSQLScripDisableIndex ='' set @TSQLScripCreationIndex='ALTER TABLE '+ QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + @is_unique_constraint + @is_primary_key + +@IndexTypeDesc + '('+@IndexColumns+') '+ case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';' print @TSQLScripCreationIndex print @TSQLScripDisableIndex fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName end close CursorIndex deallocate CursorIndex
QUERY 3 :- Script to Generate Drop and Create script of all Foreign keys for a Particular Table
This Script will provide you Create Script and Drop script of all the constraints of a Table. This script is highly useful when you are Using Import\Export utility and face any constraint related errors. You can Drop the constraints for that table, Perform Import and then again create the constraints using the script generated by below Query :-
SET NOCOUNT ON DECLARE @operation VARCHAR(10) DECLARE @tableName sysname DECLARE @schemaName sysname SET @operation = 'DROP' SET @tableName = 'TABLENAME' -- Table name SET @schemaName = 'dbo' DECLARE @cmd NVARCHAR(1000) DECLARE @FK_NAME sysname, @FK_OBJECTID INT, @FK_DISABLED INT, @FK_NOT_FOR_REPLICATION INT, @DELETE_RULE smallint, @UPDATE_RULE smallint, @FKTABLE_NAME sysname, @FKTABLE_OWNER sysname, @PKTABLE_NAME sysname, @PKTABLE_OWNER sysname, @FKCOLUMN_NAME sysname, @PKCOLUMN_NAME sysname, @CONSTRAINT_COLID INT DECLARE cursor_fkeys CURSOR FOR SELECT Fk.name, Fk.OBJECT_ID, Fk.is_disabled, Fk.is_not_for_replication, Fk.delete_referential_action, Fk.update_referential_action, OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name, schema_name(Fk.schema_id) AS Fk_table_schema, TbR.name AS Pk_table_name, schema_name(TbR.schema_id) Pk_table_schema FROM sys.foreign_keys Fk LEFT OUTER JOIN sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join WHERE TbR.name = @tableName AND schema_name(TbR.schema_id) = @schemaName OPEN cursor_fkeys FETCH NEXT FROM cursor_fkeys INTO @FK_NAME,@FK_OBJECTID, @FK_DISABLED, @FK_NOT_FOR_REPLICATION, @DELETE_RULE, @UPDATE_RULE, @FKTABLE_NAME, @FKTABLE_OWNER, @PKTABLE_NAME, @PKTABLE_OWNER WHILE @@FETCH_STATUS = 0 BEGIN -- create statement for enabling FK IF @operation = 'ENABLE' BEGIN SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] CHECK CONSTRAINT [' + @FK_NAME + ']' PRINT @cmd END -- create statement for disabling FK IF @operation = 'DISABLE' BEGIN SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] NOCHECK CONSTRAINT [' + @FK_NAME + ']' PRINT @cmd END -- create statement for dropping FK and also for recreating FK IF @operation = 'DROP' BEGIN -- drop statement SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] DROP CONSTRAINT [' + @FK_NAME + ']' PRINT @cmd -- create process DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT -- create cursor to get FK columns DECLARE cursor_fkeyCols CURSOR FOR SELECT COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name, COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name FROM sys.foreign_keys Fk LEFT OUTER JOIN sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID WHERE TbR.name = @tableName AND schema_name(TbR.schema_id) = @schemaName AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008 ORDER BY Fk_Cl.constraint_column_id OPEN cursor_fkeyCols FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME SET @COUNTER = 1 SET @FKCOLUMNS = '' SET @PKCOLUMNS = '' WHILE @@FETCH_STATUS = 0 BEGIN IF @COUNTER > 1 BEGIN SET @FKCOLUMNS = @FKCOLUMNS + ',' SET @PKCOLUMNS = @PKCOLUMNS + ',' END SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']' SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']' SET @COUNTER = @COUNTER + 1 FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME END CLOSE cursor_fkeyCols DEALLOCATE cursor_fkeyCols -- generate create FK statement SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] WITH ' + CASE @FK_DISABLED WHEN 0 THEN ' CHECK ' WHEN 1 THEN ' NOCHECK ' END + ' ADD CONSTRAINT [' + @FK_NAME + '] FOREIGN KEY (' + @FKCOLUMNS + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] (' + @PKCOLUMNS + ') ON UPDATE ' + CASE @UPDATE_RULE WHEN 0 THEN ' NO ACTION ' WHEN 1 THEN ' CASCADE ' WHEN 2 THEN ' SET_NULL ' END + ' ON DELETE ' + CASE @DELETE_RULE WHEN 0 THEN ' NO ACTION ' WHEN 1 THEN ' CASCADE ' WHEN 2 THEN ' SET_NULL ' END + '' + CASE @FK_NOT_FOR_REPLICATION WHEN 0 THEN '' WHEN 1 THEN ' NOT FOR REPLICATION ' END PRINT @cmd END FETCH NEXT FROM cursor_fkeys INTO @FK_NAME,@FK_OBJECTID, @FK_DISABLED, @FK_NOT_FOR_REPLICATION, @DELETE_RULE, @UPDATE_RULE, @FKTABLE_NAME, @FKTABLE_OWNER, @PKTABLE_NAME, @PKTABLE_OWNER END CLOSE cursor_fkeys DEALLOCATE cursor_fkeys
QUERY 4 :- Script to generate Create and Drop script of all Indexes of all tables in a Database
This wonderful script will provide create and drop script of all Indexes in a database. This Script is highly useful in case you are migrating few tables using Import\Export of BCP on new database and now you want to create similar indexes on destination tables as available on source.
Select A.[object_id] , OBJECT_NAME(A.[object_id]) AS Table_Name , A.Index_ID , A.[Name] As Index_Name , CAST( Case When A.type = 1 AND is_unique = 1 Then 'Create Unique Clustered Index ' When A.type = 1 AND is_unique = 0 Then 'Create Clustered Index ' When A.type = 2 AND is_unique = 1 Then 'Create Unique NonClustered Index ' When A.type = 2 AND is_unique = 0 Then 'Create NonClustered Index ' End + quotename(A.[Name]) + ' On ' + quotename(S.name) + '.' + quotename(OBJECT_NAME(A.[object_id])) + ' (' + Stuff( ( Select ',[' + COL_NAME(A.[object_id],C.column_id) + Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End From sys.index_columns C WITH (NOLOCK) Where A.[Object_ID] = C.object_id And A.Index_ID = C.Index_ID And C.is_included_column = 0 Order by C.key_Ordinal Asc For XML Path('') ) ,1,1,'') + ') ' + CASE WHEN A.type = 1 THEN '' ELSE Coalesce('Include (' + Stuff( ( Select ',' + QuoteName(COL_NAME(A.[object_id],C.column_id)) From sys.index_columns C WITH (NOLOCK) Where A.[Object_ID] = C.object_id And A.Index_ID = C.Index_ID And C.is_included_column = 1 Order by C.index_column_id Asc For XML Path('') ) ,1,1,'') + ') ' ,'') End + Case When A.has_filter = 1 Then 'Where ' + A.filter_definition Else '' End + ' With (Drop_Existing = ON, SORT_IN_TEMPDB = ON' --when the same index exists you'd better to set the Drop_Existing = ON --SORT_IN_TEMPDB = ON is recommended but based on your own environment. + ', Fillfactor = ' + Cast(Case When fill_factor = 0 Then 100 Else fill_factor End As varchar(3)) + Case When A.[is_padded] = 1 Then ', PAD_INDEX = ON' Else ', PAD_INDEX = OFF' END + Case When D.[no_recompute] = 1 Then ', STATISTICS_NORECOMPUTE = ON' Else ', STATISTICS_NORECOMPUTE = OFF' End + Case When A.[ignore_dup_key] = 1 Then ', IGNORE_DUP_KEY = ON' Else ', IGNORE_DUP_KEY = OFF' End + Case When A.[ALLOW_ROW_LOCKS] = 1 Then ', ALLOW_ROW_LOCKS = ON' Else ', ALLOW_ROW_LOCKS = OFF' END + Case When A.[ALLOW_PAGE_LOCKS] = 1 Then ', ALLOW_PAGE_LOCKS = ON' Else ', ALLOW_PAGE_LOCKS = OFF' End + Case When P.[data_compression] = 0 Then ', DATA_COMPRESSION = NONE' When P.[data_compression] = 1 Then ', DATA_COMPRESSION = ROW' Else ', DATA_COMPRESSION = PAGE' End + ') On ' + Case when C.type = 'FG' THEN quotename(C.name) ELSE quotename(C.name) + '(' + F.Partition_Column + ')' END + ';' --if it uses partition scheme then need partition column As nvarchar(Max)) As Index_Create_Statement , C.name AS FileGroupName , 'DROP INDEX ' + quotename(A.[Name]) + ' On ' + quotename(S.name) + '.' + quotename(OBJECT_NAME(A.[object_id])) + ';' AS Index_Drop_Statement From SYS.Indexes A WITH (NOLOCK) INNER JOIN sys.objects B WITH (NOLOCK) ON A.object_id = B.object_id INNER JOIN SYS.schemas S ON B.schema_id = S.schema_id INNER JOIN SYS.data_spaces C WITH (NOLOCK) ON A.data_space_id = C.data_space_id INNER JOIN SYS.stats D WITH (NOLOCK) ON A.object_id = D.object_id AND A.index_id = D.stats_id Inner Join --The below code is to find out what data compression type was used by the index. If an index is not partitioned, it is easy as only one data compression --type can be used. If the index is partitioned, then each partition can be configued to use the different data compression. This is hard to generalize, --for simplicity, I just use the data compression type used most for the index partitions for all partitions. You can later rebuild the index partition to --the appropriate data compression type you want to use ( select object_id, index_id, Data_Compression, ROW_NUMBER() Over(Partition By object_id, index_id Order by COUNT(*) Desc) As Main_Compression From sys.partitions WITH (NOLOCK) Group BY object_id, index_id, Data_Compression ) P ON A.object_id = P.object_id AND A.index_id = P.index_id AND P.Main_Compression = 1 Outer APPLY ( SELECT COL_NAME(A.object_id, E.column_id) AS Partition_Column From sys.index_columns E WITH (NOLOCK) WHERE E.object_id = A.object_id AND E.index_id = A.index_id AND E.partition_ordinal = 1 ) F Where A.type IN (1,2) --clustered and nonclustered AND B.Type != 'S' AND OBJECT_NAME(A.[object_id]) not like 'queue_messages_%' AND OBJECT_NAME(A.[object_id]) not like 'filestream_tombstone_%' AND OBJECT_NAME(A.[object_id]) not like 'sys%' --if you have index start with sys then remove it
QUERY 5 :- Script to find space used by each Index in a database
-- Ensure a USE statement has been executed first. SELECT [DatabaseName] ,[ObjectId] ,[ObjectName] ,[IndexId] ,[IndexDescription] ,CONVERT(DECIMAL(16, 1), (SUM([avg_record_size_in_bytes] * [record_count]) / (1024.0 * 1024))) AS [IndexSize(MB)] ,[lastupdated] AS [StatisticLastUpdated] ,[AvgFragmentationInPercent] FROM ( SELECT DISTINCT DB_Name(Database_id) AS 'DatabaseName' ,OBJECT_ID AS ObjectId ,Object_Name(Object_id) AS ObjectName ,Index_ID AS IndexId ,Index_Type_Desc AS IndexDescription ,avg_record_size_in_bytes ,record_count ,STATS_DATE(object_id, index_id) AS 'lastupdated' ,CONVERT([varchar](512), round(Avg_Fragmentation_In_Percent, 3)) AS 'AvgFragmentationInPercent' FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'detailed') WHERE OBJECT_ID IS NOT NULL AND Avg_Fragmentation_In_Percent <> 0 ) T GROUP BY DatabaseName ,ObjectId ,ObjectName ,IndexId ,IndexDescription ,lastupdated ,AvgFragmentationInPercent
I shall be back with more queries soon. Please comment your views. Thanks for reading!!!
2 responses to “SQL DBA Cheatcode Part 4 : Few Important Queries for any DBA”
[…] SQL DBA Cheatcode Part 4 : Few Important Queries for any DBA […]
[…] Few Important Queries for any DBA – Table and constraints […]