Find indexes and PK for each table in DB

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

SQL Server 2000 Forums

SQL Server Development (2000)

Find indexes and PK for each table in DB

Author

Topic

rosejr
Starting Member

12 Posts

Posted - 2008-10-07 : 17:22:32

I need a query (probably sys tables) to return...

Table name
Index (columns)
PK (columns)

This needs to be similar to when [alt](F1] when a table is highlighted. But I want to run it for ALL tables at once, to export to an excel spreadsheet.

Any ideas?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-07 : 17:26:26

Exec sp_Msforeachtable @command1='sp_helpindex ''?'''

rosejr
Starting Member

12 Posts

Posted - 2008-10-08 : 10:35:29

Thanks, that worked great. But I don't see the table name. Is there a way to add that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:37:08

quote:
Originally posted by rosejr

Thanks, that worked great. But I don't see the table name. Is there a way to add that?

thats because you asked code to return info for all tables. just use

sp_helpindex 'yourtablename'

to get details for the requested table alone

rosejr
Starting Member

12 Posts

Posted - 2008-10-08 : 10:43:05

quote:
Originally posted by visakh16

quote:
Originally posted by rosejr

Thanks, that worked great. But I don't see the table name. Is there a way to add that?

thats because you asked code to return info for all tables. just use

sp_helpindex 'yourtablename'

to get details for the requested table alone

Right but I am wanting to run it for all tables. I have about 700 tables and don't want to have to run it for each table. I want a script I can run quickly and export to excel.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:48:52

thats what script posted by SOdeep does. the sp_Msforeachtable sp loops through all tables and runs the sp_helpindex sp for each table.

rosejr
Starting Member

12 Posts

Posted - 2008-10-08 : 10:53:35

But it doesn't display the table name. I would like to group the output by the table name. Is there a way to add the table name to the output.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:58:10

try this too

SELECT *
FROM sysindexes i
inner join sysobjects o
on o.Id=i.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources