Wednesday, October 19, 2011

SQL Server 2008 : Query to get list of Indexes from the single Database

This script will provide list of the indexes with following information Database Name,Table Name, Index Name, comma separated list of columns used in Index,  comma separated list of columns INCLUDED in Index and type of Index (Clustered or non Clustered) from a given database.


USE <Database Name>
GO

DECLARE
@IndexCount INT,
@Columns varchar(2000) = '',
@IncludedColumns varchar(2000) = '',
@IndexName VARCHAR(1000) = ''


DECLARE @tblIndex TABLE (IndexName VARCHAR(1000), ColumnName VARCHAR(100), TableName VARCHAR(100), IndexType VARCHAR(100) , IsIncluded BIT)
DECLARE @tblIndexDetail TABLE (RecordID INT Identity(1,1), DataBaseName VARCHAR(100), TableName VARCHAR(100), IndexName VARCHAR(1000), ColumnName VARCHAR(1000), IncludedColumns VARCHAR(2000), IndexType VARCHAR(100))

INSERT INTO @tblIndex (IndexName, ColumnName, TableName, IndexType, IsIncluded)
SELECT IND.NAME AS IndexName, COL.NAME AS ColumnName, T.Name AS TableName, IND.TYPE_DESC AS IndexType , IC.IS_INCLUDED_COLUMN AS IsIncluded
FROM SYS.INDEXES IND
INNER JOIN SYS.INDEX_COLUMNS IC ON IND.OBJECT_ID = IC.OBJECT_ID AND IND.INDEX_ID = IC.INDEX_ID
INNER JOIN SYS.COLUMNS COL ON IC.OBJECT_ID = COL.OBJECT_ID AND IC.COLUMN_ID = COL.COLUMN_ID
INNER JOIN SYS.TABLES T ON IND.OBJECT_ID = T.OBJECT_ID
ORDER BY T.NAME, IND.NAME, IND.INDEX_ID, IC.INDEX_COLUMN_ID


SELECT @IndexCount = COUNT(*) FROM @tblIndex
WHILE @IndexCount > 0
BEGIN

SELECT TOP 1 @IndexName = IndexName FROM @tblIndex

SELECT @Columns = @Columns + ColumnName + ', ' FROM @tblIndex WHERE IndexName = @IndexName AND IsIncluded != 1
IF LEN(@Columns) > 1
SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)

SELECT @IncludedColumns = @IncludedColumns + ColumnName + ', ' FROM @tblIndex WHERE IndexName = @IndexName AND IsIncluded = 1
IF LEN(@IncludedColumns) > 1
SET @IncludedColumns = LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1)

INSERT INTO @tblIndexDetail (DataBaseName, TableName, IndexName, ColumnName, IncludedColumns, IndexType)
SELECT DB_Name() AS DatabaseName, TableName, IndexName, @Columns, @IncludedColumns, IndexType
FROM  @tblIndex
WHERE IndexName = @IndexName
GROUP BY TableName, IndexName, IndexType

DELETE FROM @tblIndex WHERE IndexName = @IndexName
SELECT @IndexCount = COUNT(*) FROM @tblIndex
SET @Columns = ''
SET @IncludedColumns = ''

END

SELECT * FROM @tblIndexDetail
GO

1 comment:

  1. If the dealer has a two or three, hold hitting until you reach thirteen or above. Stand in case your hand is a tough 17 or larger, meaning that an A has not been used to achieve 우리카지노 that whole. With Bet Behind, you don’t need to worry about tables being full. You don’t have to dress a lot as} go to a on line casino when find a way to|you possibly can} play from the consolation of your house home} or on the go. Its additionally essential to note that the video stream is made out there by a regulated third-party supplier, so the whole process is protected and secure.

    ReplyDelete