Just Code‎ > ‎

TSQL - Generate WikiMarkup for all Indexes (all key columns and included columns)

posted May 8, 2012, 7:57 AM by Peter Henell   [ updated May 8, 2012, 8:01 AM ]
If you document your indexes using extended properties then you can use this snippet to generate Wikimarkup for all the indexes in your database.
The information will include
  • Name of index
  • Type of index
  • Is the index unique?
  • Is the index Primary Key?
  • The key columns ( columns will be presented colon separated)
  • The columns that are Included in the index  ( columns will be presented colon separated)

;with InformationSchemaTables as 
(    
      SELECT            
         name as Table_name,
         SCHEMA_NAME(schema_id ) as Table_Schema,
         object_id
      FROM 
         sys.objects 
      where 
        type = 'u' and is_ms_shipped = 0
)

SELECT 
    WikiMarkup FROM 
    InformationSchemaTables iss
CROSS APPLY(

        SELECT '||' + iss.TABLE_SCHEMA + '.' + iss.TABLE_NAME + ' || Index Name || Index Type || Unique || Primary key || Key Columns || Included Columns ||' as WikiMarkup 

        UNION ALL

        SELECT '|' + ISNULL( 
                    ( SELECT CAST(VALUE AS NVARCHAR(MAX)) FROM fn_listextendedProperty('Description', 
                     'SCHEMA', iss.TABLE_SCHEMA
                     ,'TABLE', iss.TABLE_NAME
                     , CASE is_unique WHEN 1 THEN 'CONSTRAINT' ELSE 'INDEX' END, name))
             , '{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}')  
             + '|' + ind.name
             + '|' + cast(type_desc as varchar(max)) COLLATE SQL_Latin1_General_CP1_CI_AS
             + '|' + case is_unique when 1 then 'Yes' else 'No' end
             + '|' + case is_primary_key when 1 then 'Yes' else 'No' end
             + '|' + isnull(substring(KeyColumns, 0, len(KeyColumns)) , ' ')
             + '|' + isnull(substring(IncludedColumns, 0, len(IncludedColumns)) , ' ')
             + '|'
             
             
        from 
            sys.indexes ind
        CROSS APPLY ( SELECT col.name + ', ' 
                     FROM sys.index_columns cols
                     INNER JOIN sys.columns col 
                        ON cols.object_id = col.object_id and cols.column_id = col.column_id 
                    WHERE cols.index_id = ind.index_id and cols.object_id = ind.object_id and cols.is_included_column = 0 --and key_ordinal > 0
                    ORDER BY cols.key_ordinal
                      FOR XML PATH('') )  D ( KeyColumns )
        CROSS APPLY
        ( SELECT col.name + ', ' 
                     FROM sys.index_columns cols
                     INNER JOIN sys.columns col 
                        ON cols.object_id = col.object_id and cols.column_id = col.column_id                      
                    WHERE cols.index_id = ind.index_id and cols.object_id = ind.object_id and cols.is_included_column = 1 --and key_ordinal = 0
                    ORDER BY cols.index_column_id
                      FOR XML PATH('') )  DE ( IncludedColumns )
            
        where
            object_id = iss.object_id 
            and ind.type_desc <> 'HEAP'
            
        union all 
        
        select '\\'

) AllTheGoodies



Example WikiMarkup Output
||Person.StateProvince || Index Name || Index Type || Unique || Primary key || Key Columns || Included Columns ||
|{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}|PK_StateProvince_StateProvinceID|CLUSTERED|Yes|Yes|StateProvinceID| |
|{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}|AK_StateProvince_Name|NONCLUSTERED|Yes|No|Name| |
|{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}|AK_StateProvince_StateProvinceCode_CountryRegionCode|NONCLUSTERED|Yes|No|StateProvinceCode, CountryRegionCode| |
|{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}|AK_StateProvince_rowguid|NONCLUSTERED|Yes|No|rowguid| |
\\
||Production.ProductModelIllustration || Index Name || Index Type || Unique || Primary key || Key Columns || Included Columns ||
|{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}|PK_ProductModelIllustration_ProductModelID_IllustrationID|CLUSTERED|Yes|Yes|ProductModelID, IllustrationID| |
\\
||dbo.DatabaseLog || Index Name || Index Type || Unique || Primary key || Key Columns || Included Columns ||
|{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}|PK_DatabaseLog_DatabaseLogID|NONCLUSTERED|Yes|Yes|DatabaseLogID| |
\\
||Production.ProductModelProductDescriptionCulture || Index Name || Index Type || Unique || Primary key || Key Columns || Included Columns ||
|{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}|PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID|CLUSTERED|Yes|Yes|ProductModelID, ProductDescriptionID, CultureID| |

Example From AdventureWorks, none of the indexes are documented using Extended properties, therefore you will get the RED warning about missing description.

Person.StateProvince Index Name Index Type Unique Primary key Key Columns Included Columns
DESCRIPTION MISSING PK_StateProvince_StateProvinceID CLUSTERED Yes Yes StateProvinceID  
DESCRIPTION MISSING AK_StateProvince_Name NONCLUSTERED Yes No Name  
DESCRIPTION MISSING AK_StateProvince_StateProvinceCode_CountryRegionCode NONCLUSTERED Yes No StateProvinceCode, CountryRegionCode  
DESCRIPTION MISSING AK_StateProvince_rowguid NONCLUSTERED Yes No rowguid  


Production.ProductModelIllustration Index Name Index Type Unique Primary key Key Columns Included Columns
DESCRIPTION MISSING PK_ProductModelIllustration_ProductModelID_IllustrationID CLUSTERED Yes Yes ProductModelID, IllustrationID  


dbo.DatabaseLog Index Name Index Type Unique Primary key Key Columns Included Columns
DESCRIPTION MISSING PK_DatabaseLog_DatabaseLogID NONCLUSTERED Yes Yes DatabaseLogID  


Production.ProductModelProductDescriptionCulture Index Name Index Type Unique Primary key Key Columns Included Columns
DESCRIPTION MISSING PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID CLUSTERED Yes Yes ProductModelID, ProductDescriptionID, CultureID  


dbo.ErrorLog Index Name Index Type Unique Primary key Key Columns Included Columns
DESCRIPTION MISSING PK_ErrorLog_ErrorLogID CLUSTERED Yes Yes ErrorLogID  


Sales.Store Index Name Index Type Unique Primary key Key Columns Included Columns
DESCRIPTION MISSING PK_Store_CustomerID CLUSTERED Yes Yes CustomerID  
Comments