March 6, 2002 at 7:51 am
Does anyone have a script that can be used to display all indexes on a database for each table without having to use the sp_helpindex for each individual table one at a time. I have a large database with many tables and indexes on most.
Rob DeMotsis
Sr. SQL Server DBA
Pier 1 Imports, Inc.
Rob DeMotsis
Sr. SQL Server DBA
March 6, 2002 at 9:20 am
Any time you need to do for all tables sp_MSForEachTable is great. FOr you needs you do this.
sp_MSForEachTable 'sp_helpindex ''?'''
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 6, 2002 at 9:22 am
Sorry, you need to do this way that way you know what table Indexes are for.
sp_MSForEachTable '
SELECT ''?'' AS Tbl
EXEC sp_helpindex ''?'''
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 30, 2002 at 8:28 am
This would probably help with what you need:
select left(o.name, 30) 'Table Name', left(i.name, 30) 'Index Name', i.indid, i.status from sysobjects o inner join sysindexes i
on o.id = i.id and o.type = 'U'
--and i.status = 2 --unique index
and i.indid <> 0 and i.indid <> 255 -- 2 -non clustered index
and o.name not in ('dtproperties')
order by o.name
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
May 1, 2002 at 12:14 am
If you want to create your own procedures for a particular requirement instead of the functionality given.
Better use sp_helptext on the procedure in master to get the procedure code and analyse that and create your own procedure.
Or otherwise craete a table with the same as recordset returned and populate the resultset of the procedure in to the table and then do processing....
here is the code for that......
create table tmpLogspace
(
DataBaseName varchar(100),
LogSize varchar(100),
LogSpaceUsed varchar(100),
Status varchar(100)
)
INSERT INTO tmpLogspace
EXEC ('DBCC SQLPERF ( LOGSPACE )')
same way replace any command in the quotes....
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
May 1, 2002 at 5:17 am
Thanks everyone for the feedback. I think I can now identify ny indexes and handle the rebuild requests a little easier, now that I know where and what they are.
Rob DeMotsis
Sr. SQL Server DBA
Pier 1 Imports, Inc.
Rob DeMotsis
Sr. SQL Server DBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply