Find table Indexes with T-SQL
-
I want to create a procedure to query my tables to identify all of the Indexes.
The information that I would like to see would be the Index Name, it's fields & direction. I also want to know which index is the clustered one (can I have more than one?).
I would like to get the Primary Key info, too.
This is more for me to create documentation from.
Would there be any other info that would be helpful? I might like to see the relationships between tables (if there are any).
Thank you,
Bryan Clauss
-
Does this work for u
sp_helpindex <tablename>
it gives the indexname , index description, index keys
-
A starting point would be to use sp_helpindex.
This tells you some information about the indexes for a given table.
If you wish to expand on what this gives you, you can script that stored procedure into query analyser (just find it in the master db and right click then follow your nose).
Once you have it scripted, it is not too difficult to change the queries (don't try to recreate the stored proc though) to suit. It also gives you an insight into how and where SQL Server puts all of its object references
-
Would it be a great idea to read some headlines on main page before entering forum?
http://qa.sqlservercentral.com/columnists/AVigneau/boostyourperformancebadindexdetection.asp
_____________
Code for TallyGenerator -
Also look at the information_schema views - look in BOL index for "information schema views". These are the nice and supported way to retrieve metadata from SQL - although I'm not sure if they give you the indices.
-
Bryan,
rsharma posted a script for info on indexes, I think it would help you. Under sql server admin: 5th thread.
Jules
Jules Bui
IT Operations DBA
Backup and Restore Administrator -
Here's a script I wrote to standardize the names of the PKs - you can probably modify it to pull any other info you want from the system table
select 'EXECUTE sp_rename ''' + b.name + ''', ' + '''PK_' + a.name + ''', ' + '''OBJECT''' from sysobjects a inner join sysobjects b on a.id = b.parent_obj where b.type = 'K' and b.name not in ('pk_dba_replication', 'pk_dtproperties')
Similarly, here's one for the indexes:
SELECT CASE WHEN c.keyno = 1 THEN 'EXECUTE' ELSE 'COMPOUND INDEX' END + ' sp_rename ''' + a.name +'.' + b.name + ''', ' + '''IX_' + a.name + '_' + d.name + ''', ' + '''INDEX'''
--select b.name, a.name, d.name, C.*
FROM sysobjects A join sysindexes B on a.id = b.id
join sysindexkeys C on B.id = C.id and b.indid = c.indid
join syscolumns d on c.id = d.id and c.colid = d.colid
where a.id > 100
and b.keycnt > 1
and c.keyno = 1 -- remove this line to see an addl row for compound indexes and b.name not like '_WA%'
and b.name not like 'PK%'
and a.name <> 'MSreplication_subscriptions'
and a.name <> 'MSsubscription_agents'
order by a.name, b.name
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply