Credits:
Author: Unknown/ChillyDBA
Date: 7 Nov 2010/7 Jul 2012
20 Jun 2014 - ChillyDBA - Bracketed the returned index key columns to allow for spaces in column names
Description
Returns a list of indexes for one or all tables in the database (depending on input parameter).
All index key columns are returned.
If a table has no indexes, then one row with index type HEAP is returned.
I converted to a table-valued function and augmented the columns returned to included attributes that are consumed by the SP that produces the CREATE/DROP Index TSQL.
Code
Function:
DROP FUNCTION dbo.udf_GetIndexColumns GO
CREATE FUNCTION dbo.udf_GetIndexColumns(@TableName SYSNAME = NULL) RETURNS @udf_IndexColumns TABLE ( DatabaseName SYSNAME NULL, TableSchema SYSNAME NULL, TableName SYSNAME NULL, IndexName SYSNAME NULL, IndexType VARCHAR(20), IndexColumns VARCHAR(1000), IndexColumns_SO VARCHAR(1000), IncludedColumns VARCHAR(1000), IncludedColumns_SO VARCHAR(1000), StatisticsLastUpdated DATETIME, FileGroupName SYSNAME NULL, Fill_Factor INT, IsPadded INT, NoRecompute INT, AllowRowLocks INT, AllowPageLocks INT ) AS /*************************************************************** Purpose: Lists the indexes and their columns Also lists hepa tables, but as every table has a heap, these are restricted to tables with no other index definitions
The following DMVs are used: sys.indexes - list all index details in the db sys.index_columns - list all index column details in the db sys.tables - list all table details in the db sys.all_columns - list all columns detail in the db Due to the use of CTE, this is valid for SQL 20065 and greater For conversion to SQL 2000, the CTE code could be used to populate a temp table Author: Unknown History: 7 Nov 2010 - Initial Issue 6 Jul 2012 - ChillyDBA - Converted to function and enhanced with schema and stats date 7 Jul 2012 - ChillyDBA - Augmented results to encompass included columns plus versions of the columns lists with sort order, plus some essential index creation flags. 23 Jun 2014 - ChillyDBA - added [] around column names returned to insulate against special characters or spaces in names.
****************************************************************/
BEGIN WITH AllIndexes_CTE ( TableSchema, TableName, IndexName, IndexType , IndexColumns, IndexColumns_SO, IncludedColumns, IncludedColumns_SO, StatisticsLastUpdated, FileGroupName, Fill_Factor, IsPadded, NoRecompute, AllowRowLocks, AllowPageLocks ) AS -- identify indexes of all types ( SELECT SCHEMA_NAME(Tab.schema_id) AS TableSchema, Tab.[name] AS TableName, IND.[name] AS IndexName, IND.Type_Desc AS IndexType, SUBSTRING ( ( SELECT ', [' + AC.name + ']' FROM sys.tables AS T INNER JOIN sys.indexes I ON T.OBJECT_ID = I.[object_id] INNER JOIN sys.index_columns IC ON I.OBJECT_ID = IC.[object_id] AND I.index_id = IC.[index_id] INNER JOIN sys.all_columns AC ON T.OBJECT_ID = AC.[object_id] AND IC.column_id = AC.[column_id] WHERE Ind.OBJECT_ID = i.[object_id] AND ind.index_id = i.index_id AND IC.is_included_column = 0 ORDER BY IC.key_ordinal FOR XML PATH('') ), 2, 8000 ) AS IndexColumns, SUBSTRING ( ( SELECT ', [' + AC.name + CASE WHEN ic.is_descending_key = 1 THEN '] DESC' ELSE '] ASC' END FROM sys.tables AS T INNER JOIN sys.indexes I ON T.OBJECT_ID = I.[object_id] INNER JOIN sys.index_columns IC ON I.OBJECT_ID = IC.[object_id] AND I.index_id = IC.[index_id] INNER JOIN sys.all_columns AC ON T.OBJECT_ID = AC.[object_id] AND IC.column_id = AC.[column_id] WHERE Ind.OBJECT_ID = i.[object_id] AND ind.index_id = i.index_id AND IC.is_included_column = 0 ORDER BY IC.key_ordinal FOR XML PATH('') ), 2, 8000 ) AS IndexColumns_SO, SUBSTRING ( ( SELECT ', [' + AC.name + ']' FROM sys.tables AS T INNER JOIN sys.inddexes I ON T.OBJECT_ID = I.[object_id] INNER JOIN sys.index_columns IC ON I.OBJECT_ID = IC.[object_id] AND I.index_id = IC.[index_id] INNER JOIN sys.all_columns AC ON T.OBJECT_ID = AC.[object_id] AND IC.column_id = AC.[column_id] WHERE Ind.OBJECT_ID = i.[object_id] AND ind.index_id = i.index_id AND IC.is_included_column = 1 ORDER BY IC.key_ordinal FOR XML PATH('') ), 2, 8000 ) AS IncludedColumns, SUBSTRING ( ( SELECT ', [' + AC.name + ']' -- no sort order on an included column FROM sys.tables AS T INNER JOIN sys.indexes I ON T.OBJECT_ID = I.[object_id] INNER JOIN sys.index_columns IC ON I.OBJECT_ID = IC.[object_id] AND I.index_id = IC.[index_id] INNER JOIN sys.all_columns AC ON T.OBJECT_ID = AC.[object_id] AND IC.column_id = AC.[column_id] WHERE Ind.OBJECT_ID = i.[object_id] AND ind.index_id = i.index_id AND IC.is_included_column = 1 ORDER BY IC.key_ordinal FOR XML PATH('') ), 2, 8000 ) AS IncludedColumns_SO, STATS_DATE(TAB.OBJECT_ID, IND.Index_ID) AS StatisticsLastUpdated, fg.name AS FileGroupName, CASE WHEN Ind.Fill_Factor = 0 THEN 100 ELSE Ind.Fill_Factor END AS Fill_Factor, Ind.Is_Padded AS IsPadded, s.No_Recompute AS NoRecompute, Ind.allow_row_locks AS AllowRowLocks, Ind.allow_page_locks AS AllowPageLocks FROM sys.indexes Ind INNER JOIN sys.stats s ON Ind.name = s.name INNER JOIN sys.tables AS Tab ON Tab.OBJECT_ID = Ind.OBJECT_ID LEFT JOIN sys.filegroups FG ON Ind.data_space_id = FG.data_space_id WHERE tab.Name = ISNULL(@TableName, tab.Name) AND Ind.Name IS NOT NULL AND INDEXPROPERTY(Ind.OBJECT_ID, Ind.Name, 'IsStatistics') = 0 ) INSERT @udf_IndexColumns ( DatabaseName, TableSchema, TableName, IndexName, IndexType, IndexColumns, IndexColumns_SO, IncludedColumns, IncludedColumns_SO, StatisticsLastUpdated, FileGroupName, Fill_Factor, IsPadded, NoRecompute, AllowRowLocks, AllowPageLocks ) -- list the clustered and nonclustered SELECT DB_NAME() AS DatabaseName, cte1.TableSchema AS TableSchema, cte1.TableName AS TableName, cte1.IndexName AS IndexName, cte1.IndexType AS IndexName, cte1.IndexColumns AS IndexColumns, cte1.IndexColumns_SO AS IndexColumns_SO, cte1.IncludedColumns AS IncludedColumns, cte1.IncludedColumns_SO AS IncludedColumns_SO, cte1.StatisticsLastUpdated AS StatisticsLastUpdated, cte1.FileGroupName AS FileGroupName, cte1.Fill_Factor AS Fill_Factor, cte1.IsPadded AS IsPadded , cte1.NoRecompute AS NoRecompute , cte1.AllowRowLocks AS AllowRowLocks , cte1.AllowPageLocks AS AllowPageLocks FROM AllIndexes_CTE cte1 WHERE IndexType IN ('CLustered', 'NonClustered')
UNION
-- list the heaps, but only where there are no other indexes ie 0 indexes on table and only a heap entry SELECT DB_NAME() AS DatabaseName, cte2.TableSchema AS TableSchema, cte2.TableName AS TableName, cte2.IndexName AS IndexName, cte2.IndexType AS IndexName, cte2.IndexColumns AS IndexColumns , cte2.IndexColumns_SO AS IndexColumns_SO , cte2.IncludedColumns AS IncludedColumns, cte2.IncludedColumns_SO AS IncludedColumns_SO, NULL AS StatisticsLastUpdated, cte2.FileGroupName AS FileGroupName, cte2.Fill_Factor AS Fill_Factor, cte2.IsPadded AS IsPadded , cte2.NoRecompute AS NoRecompute , cte2.AllowRowLocks AS AllowRowLocks , cte2.AllowPageLocks AS AllowPageLocks FROM AllIndexes_CTE cte2 WHERE IndexType IN ('Heap') AND (SELECT COUNT(*) FROM AllIndexes_CTE cte3 WHERE cte3.TableName = cte2.TableName) = 1 ORDER BY cte1.TableName, cte1.IndexType, cte1.IndexName
RETURN END
/* USE AdventureWorksDW GO
SELECT * FROM dbo.udf_GetIndexColumns(NULL)
SELECT * FROM dbo.udf_GetIndexColumns('DimCustomer') SELECT * FROM dbo.udf_GetIndexColumns('Calendar')
*/
|
Test Code:
USE AdventureWorksDW
GO
SELECT * FROM dbo.udf_GetIndexColumns(NULL)
SELECT * FROM dbo.udf_GetIndexColumns('DimCustomer')
|
 Updating...
Andy Hughes, Jun 23, 2014, 4:39 PM
|