SQL DBA Cheatcode Part 4 : Few Important Queries for any DBA

0

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!!!

Vaibhav Gupta

  1. […] SQL DBA Cheatcode Part 4 : Few Important Queries for any DBA […]

  2. Series : SQL DBA Cheatcode - DBA THINGS June 5, 2015 at 12:57 pm

    […] Few Important Queries for any DBA – Table and constraints […]

%d bloggers like this: