October 3, 2008 at 7:14 am
Guys,
Is there anyway to find in database given a column it has index or not.
For example in all the 340 tables of the database on our instance I want to find out the instance where column 'EMPID' does not have any index.
Also is there any to find all ID columns in the database which does not have Primary key constraint.
Thanks
October 3, 2008 at 7:52 am
Check the following link
October 3, 2008 at 8:52 am
Take a look at the systems tables. sys.indexes, sys.index_columns will get you a long way there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 5, 2008 at 12:38 pm
this should make it easier:
SELECT o.name as TableName, c.name as ColumnName, i.name as IndexName
, o.schema_id, i.object_id, c.column_id, i.index_id
, i.type_desc as IndexType, ic.is_included_column
FROM (sys.columns c
Join sys.objects o ON c.object_id = o.object_id)
LEFT JOIN (sys.indexes i
Join sys.index_columns ic ON ic.index_id = i.index_id
And ic.object_id = i.object_id)
ON c.column_id = ic.column_id
And o.object_id = i.object_id
ORDER BY ColumnName, TableName, IndexName
To reuse it, just drop off the ORDER BY and make it into a View.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply