So..... there is a new application development and you get bunch of scripts from bunch of developers to be executed on UAT and subsequently PROD , right? You review the scripts look for any blunders/mistakes made but darn some scripts are just painfully long and are mostly generated from some third party software like SQL Compare or whatever.
One thing I often want to make sure is they (developers) did not create clustered indexes (PK automatically creates one - I am sure you already know that) on columns with datatypes let's say GUID ;) or varchar(max).. !! Exaggeration ?? Well be that as it may.. I have seen some blunders and I want to list all indexes with their --- type, columns they are on and the data types of those columns.
/*
All Indexes With Column Names (DataTypes) And Index Types
----------------------------------------------------------
sys.indexes === system view is used to get type of indexes
type
tinyint
Type of index:
0 = Heap
1 = Clustered
2 = Nonclustered
3 = XML
4 = Spatial
5 = Clustered xVelocity memory optimized columnstore index (Reserved for future use.)
6 = Nonclustered columnstore index
Taken from http://raresql.com/2013/03/29/sql-server-list-of-all-indexes-with-column-name-and-datatype-including-columnstore-index/
and modified so you don't have to create stored proc on production server
*/
USE Your_DB_Name --enter your database name here
GO
declare @Type int
set @type = 1 -- for clustered (Change it according to header for other index types)
BEGIN
;WITH CTE AS ( SELECT TBL.object_id AS [Object ID]
, schema_name(schema_id) AS [Schema Name]
, tbl.name AS [Table Name]
, i.name AS [Index Name]
, i.type AS [Index type]
, i.type_desc AS [Index Desc]
, clmns.name AS [Column Name]
, styps.name AS [Type Name]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0)
AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic
ON (ic.column_id > 0 and (ic.key_ordinal > 0
OR ic.partition_ordinal = 0
OR ic.is_included_column != 0))
AND (ic.index_id=CAST(i.index_id AS int)
AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns
ON clmns.object_id = ic.object_id
AND clmns.column_id = ic.column_id
INNER JOIN sys.systypes AS styps
ON clmns.system_type_id=styps.type
WHERE ((1=(CASE WHEN @Type is Null THEN 1 ELSE 0 END)
OR i.type = @Type)))
--To convert all datatypes into one row
SELECT [Object ID]
, [Schema Name]
, [Table Name]
, [Index Name]
, [Index Desc]
, STUFF((SELECT ', ' + [Column Name] + '(' + [Type Name] + ')'
FROM CTE AS CTE1
WHERE CTE1.[Object ID]= CTE2. [Object ID]
FOR XML PATH('')),1,1,'') as [Column Name with datatype]
FROM CTE AS CTE2
GROUP BY
[Object ID]
,[Schema Name]
,[Table Name]
,[Index Name]
,[Index Desc]
ORDER BY [Object ID],[Schema Name],[Table Name]
END
GO
I tried to write the query myself but my t-sql scripting skills are ___ well they are okay but I don't want to re-invent the wheel. So I just asked Google about it. My rule is : If Google won't find it, I won't write it. :)
Okay here you go -- (Thanks to Muhammad Imran from "raresql.com")
/*
All Indexes With Column Names (DataTypes) And Index Types
----------------------------------------------------------
sys.indexes === system view is used to get type of indexes
type
tinyint
Type of index:
0 = Heap
1 = Clustered
2 = Nonclustered
3 = XML
4 = Spatial
5 = Clustered xVelocity memory optimized columnstore index (Reserved for future use.)
6 = Nonclustered columnstore index
Taken from http://raresql.com/2013/03/29/sql-server-list-of-all-indexes-with-column-name-and-datatype-including-columnstore-index/
and modified so you don't have to create stored proc on production server
*/
USE Your_DB_Name --enter your database name here
GO
declare @Type int
set @type = 1 -- for clustered (Change it according to header for other index types)
BEGIN
;WITH CTE AS ( SELECT TBL.object_id AS [Object ID]
, schema_name(schema_id) AS [Schema Name]
, tbl.name AS [Table Name]
, i.name AS [Index Name]
, i.type AS [Index type]
, i.type_desc AS [Index Desc]
, clmns.name AS [Column Name]
, styps.name AS [Type Name]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0)
AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic
ON (ic.column_id > 0 and (ic.key_ordinal > 0
OR ic.partition_ordinal = 0
OR ic.is_included_column != 0))
AND (ic.index_id=CAST(i.index_id AS int)
AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns
ON clmns.object_id = ic.object_id
AND clmns.column_id = ic.column_id
INNER JOIN sys.systypes AS styps
ON clmns.system_type_id=styps.type
WHERE ((1=(CASE WHEN @Type is Null THEN 1 ELSE 0 END)
OR i.type = @Type)))
--To convert all datatypes into one row
SELECT [Object ID]
, [Schema Name]
, [Table Name]
, [Index Name]
, [Index Desc]
, STUFF((SELECT ', ' + [Column Name] + '(' + [Type Name] + ')'
FROM CTE AS CTE1
WHERE CTE1.[Object ID]= CTE2. [Object ID]
FOR XML PATH('')),1,1,'') as [Column Name with datatype]
FROM CTE AS CTE2
GROUP BY
[Object ID]
,[Schema Name]
,[Table Name]
,[Index Name]
,[Index Desc]
ORDER BY [Object ID],[Schema Name],[Table Name]
END
GO
