Thanks. The timing is perfect as I am looking at indexes at the moment with a view to leaving some out of the build as they appear not to be used.
it's a datamart staging table that I am looking at peforming a lot of idex scans whilst doing big selects. might as well table scan than index scan for the tinme it takes to build them.
This is a bottomline index check, really cool, I used to do it manually on many sqlserver environment, it took time. now the script will help me. thanks.
Great stuff. Thanks.
Only 2 notes:
WHERE <...> AND i.indid < 255 and i.name NOT LIKE '_WA_Sys_%'
eliminates NULLs and makes join to sysindexes actually INNER, not LEFT.I'm not sure if it's possible to have a table without indexes at all. It must be an index with indid either 0 or 1 for each table.
And COALESCE or ISNULL works faster than case ... IS NULL then ... else ... end.
So I have rewritten your code this way:
SELECT o.id AS TableID ,u.name Owner,o.name TableName, i.Indid AS IndexID , CASE i.name WHEN o.name THEN '** NONE **' ELSE i.name END AS IndexName, CASE i.indid WHEN 1 THEN 'CLUSTERED' WHEN 0 THEN 'HEAP' ELSE 'NONCLUSTERED' END AS ClusterType, CASE WHEN (i.status & 2048) > 0 THEN 'PRIMARY KEY' WHEN (i.status & (2|4096)) > 0 THEN 'UNIQUE' ELSE '' END AS UniqueType, CASE WHEN (i.status & (2048)) > 0 OR ((i.status & (4096)) > 0 ) THEN 'CONSTRAINT' WHEN i.indid = 0 THEN ' ' ELSE 'INDEX' END AS IndexType,-- This following part is non essential-- It is a pre char aggregate I use in other scripts -- to generate create and drop scripts COALESCE ( INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 3), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 4), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 6), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 7), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 8), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15), '') + COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16), '') AS AllColName,-- COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1), '') AS ColName1, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2), '') AS ColName2, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 3), '') AS ColName3, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 4), '') AS ColName4, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5), '') AS ColName5, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 6), '') AS ColName6, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 7), '') AS ColName7, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 8), '') AS ColName8, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9), '') AS ColName9, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10), '') AS ColName10, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11), '') AS ColName11, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12), '') AS ColName12, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13), '') AS ColName13, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14), '') AS ColName14, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15), '') AS ColName15, COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16), '') AS ColName16 FROM sysobjects o (NOLOCK) INNER JOIN sysindexes i (NOLOCK) ON o.id = i.id JOIN sysusers u (NOLOCK) ON o.uid = u.uid WHERE o.type = 'U' AND i.indid < 255 AND o.name NOT IN ('dtproperties') AND i.name NOT LIKE '_WA_Sys_%'
