Search This Blog

July 01, 2010

Display all the tables and indexes present in a SQL Server database


SELECT
st.name AS [Table Name]
,si.name AS [Index Name]
,si.id AS [Index ID]
,IS_CLUSTERED = INDEXPROPERTY(si.id, si.name, 'IsClustered')
,IS_UNIQUE = INDEXPROPERTY(si.id, si.name, 'IsUnique')
FROM sys.tables st
LEFT OUTER JOIN sys.sysindexes si
ON st.name = OBJECT_NAME(si.id)
WHERE st.type = 'u'
AND OBJECTPROPERTY(si.id, 'IsMsShipped') = 0 /*leave out system tables*/
AND (si.indid BETWEEN 1 AND 254)
AND (si.Status & 64)=0 /*leave out AUTO_STATISTIC*/
ORDER BY st.name, si.name

No comments: