November 12, 2010 at 10:56 am
Hi All,
Is there any way in which we can get the list of non-indexed columns in a table or a whole database?
Thanks in advance,
SQLRocks
November 12, 2010 at 11:08 am
Sure you can using sys.columns, sys.index_columns, etc.
But I'm curious - do you have a purpose for doing this or just curiosity?
November 12, 2010 at 11:20 am
Dangerous, getting this type of thing to a manager.
Only semi-tested, but this should get you there:
SELECT DISTINCT
OBJECT_NAME(c.object_id) AS thetable
, c.name
FROM sys.index_columns AS ic
RIGHT OUTER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id IS null
Run this on the DB for which you want the info.
Please check back with us if your manager gives you any action items/ directions around this. I just have a bad feeling I know where that manager is headed...
November 12, 2010 at 11:21 am
Tables columns that are not in an index:
selectschemas.nameas schema_name
,tables.nameas table_name
,columns.nameas column_name
fromsys.schemas
joinsys.tables
ontables.schema_id= schemas.schema_id
joinsys.columns
on columns.object_id = tables.object_id
WHERENOT EXISTS
(select 1
fromsys.index_columns
whereindex_columns.object_id= columns.object_id
andindex_columns.column_id= columns.column_id
)
SQL = Scarcely Qualifies as a Language
November 12, 2010 at 11:31 am
Pam and Carl,
Wow! How beautiful and excellent are your replies! Thank you very much,
SQLRocks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply