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.
| Author |
Topic |
|
Jim77
Constraint Violating Yak Guru
440 Posts |
 Posted - 2006-05-15 : 07:49:26
|
| Hi guysI am trying to get index information on all user tables in any given db without doing a sp_helpindex for each table, is there any way of using a system stord proc to do this?If not how would I loop through each user table and generate the information I require from the sp_helpindex please?cheers |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-05-15 : 08:08:57
|
| Select * from information_schema.table_constraintsMadhivananFailing to plan is Planning to fail |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-15 : 08:13:14
|
sp_helpindex just uses sysindexes, so you could probably trawl that yourself if you wanted.Otherwise sp_msforeachtable 'exec sp_helpindex  'Darn, it turned my [ ? ] without spaces into a -------Moo. :) |
|
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2006-05-15 : 08:55:43
|
| cheers guys Moo :) trawling through sysindexes table to me is like fishing for fresh water fish in the dead sea and I ain't no fisherman :), it looks to intimidating so I left it.... Madhi :)the information schema views idea has helped a lot thank - you, although i am only getting primary key indexs back with no non- clusterred does this mean that there are no non-clusterred in the db ? would I also have to defrag an index on a primary key as part of a maintenace routine ??One more question that is bugging me please guys is: How would you loop through all user tables and write that a sp_helpindex for each table just out of curiosity ?Thanks again for your time :) |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-15 : 08:58:24
|
quote:
How would you loop through all user tables and write that a sp_helpindex for each table just out of curiosity ?
As I said:Otherwise sp_msforeachtable 'exec sp_helpindex 'Darn, it turned my [ ? ] without spaces into a -------Moo. :) |
|
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2006-05-15 : 09:17:53
|
| sorry that don't work Moo! it s coming up with errorProcedure 'sp_helpindex' expects parameter '@objname', which was not suppliedI can't find anything on BOL for sp_MSforeachtable either ? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-15 : 09:24:09
|
| Are you putting a question mark in square brackets after the word helpindex? I can't type it because it gets changed into a question mark, but that should fix that error message.sp_msforeachtable is undocumented, but present in 2005 & 2000.-------Moo. :) |
|
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2006-05-15 : 09:28:33
|
| that's excellent thank you very much Moo.But is there any way to see the table name that the index's fall under they are all grouped by table but the table name is not clear ? |
|
|
|
|
|
|
sp_helpindex for all tables in a db