Post date: May 8, 2012 2:57:45 PM
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
;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.