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
|
