Cql_DBA: Retrive index related Information for all tables in a database

Use this scripts to find index related information for all table in a databases

SELECT

DB_NAME(DB_ID()) AS DATABASENAME

, SCHEMA_NAME(T.SCHEMA_ID) AS SCHEMANAME

, T.NAME AS TABLENAME

, I.NAME AS INDEXNAME

, I.TYPE_DESC AS INDEXTYPE

, C.NAME AS COLUMNNAME

, TYP.NAME AS DATATYPE

, IC.IS_INCLUDED_COLUMN

, C.IS_IDENTITY

, I.IS_PRIMARY_KEY

, I.IS_UNIQUE_CONSTRAINT

, IC.IS_DESCENDING_KEY

, I.IS_UNIQUE

, I.FILL_FACTOR

FROM SYS.TABLES 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.COLUMNS C ON IC.OBJECT_ID =C.OBJECT_ID

AND IC.COLUMN_ID = C.COLUMN_ID

INNER JOIN SYS.TYPES TYP ON C.USER_TYPE_ID =TYP.USER_TYPE_ID

WHERE 1=1

AND T.IS_MS_SHIPPED = 0

AND I.IS_HYPOTHETICAL = 0

ORDER BY DATABASENAME, SCHEMANAME, TABLENAME,INDEXNAME, IC.IS_INCLUDED_COLUMN, IC.KEY_ORDINAL

Cql_DBA