Query to list all Indexes + more? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query to list all Indexes + more?

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-07-29 : 03:10:57

Hi

Is there a way of listing all Indexes on a table which also lists the INCLUDE list?

e.g. I have Indexes on my tables like this:-

CREATE NONCLUSTERED INDEX [index1234] ON [dbo].[table1]
(
[PCode] ASC
)
INCLUDE ( [ERecno],
[ADate],
[Postcode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Does anyone know how to do this please?

Thanks

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-07-29 : 03:48:18

I have this script:

SELECT
OBJECT_NAME (ss.OBJECT_ID ) AS TABLE_NAME,
COALESCE (si.type_desc, '***NO INDEX***' ) AS INDEX_TYPE,
CASE si.is_unique WHEN 1 THEN 'Y' ELSE 'N' END AS UNIQUE_INDEX,
CASE si.is_primary_key WHEN 1 THEN 'Y' ELSE 'N' END AS PRIMARY_KEY,
CASE si.is_unique_constraint WHEN 1 THEN 'Y' ELSE 'N' END AS UNIQUE_CONSTRAINT,
ss.name AS STATISTIC_NAME,
CASE WHEN si.name IS NOT NULL THEN STATS_DATE (t.OBJECT_ID , si.index_id)
ELSE STATS_DATE (t.OBJECT_ID , ss.stats_id)
END as LAST_UPDATE_STATS,
STUFF ((SELECT ', ' + c.name
FROM sys.stats_columns AS sc
INNER JOIN sys.columns AS c
ON sc.OBJECT_ID = c.OBJECT_ID AND sc.column_id = c.column_id
WHERE ss.OBJECT_ID = sc.OBJECT_ID AND ss.stats_id = sc.stats_id
ORDER BY c.column_id FOR XML PATH('')), 1, 1, '') AS INDEX_COLUMNS,
CASE auto_created WHEN 1 THEN 'Y' ELSE 'N' END AS STAT_AUTO_CREATED,
CASE user_created WHEN 1 THEN 'Y' ELSE 'N' END AS STAT_CREATED_BY_USER
FROM sys.stats AS ss
inner join sys.tables AS t
ON OBJECT_NAME (ss.OBJECT_ID ) = t.name
LEFT OUTER JOIN sys.indexes AS si
ON ss.name = si.name
WHERE t.is_ms_shipped = 0
--AND auto_created <> 1 --comment out this line to include stats auto created by SQL SERVER
ORDER BY t.name, COALESCE (si.index_id, 999)

which will give me most of the info, but it still does not list all the fields in the INCLUDE statement above?
How can I add them in as well? I can't find reference to them anywhere?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-30 : 14:07:53

sp_helpindexes 'your table'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources