January 27, 2009 at 8:41 am
create table #tt(tablename varchar(500),index_name varchar(500),index_Description varchar(500),index_Keys varchar(500))
create table #tmp ( id int identity , tablename varchar(500) )
insert into #tmp (tablename)
select distinct obj.name
from sysobjects obj
inner join sysindexes indx on obj.id = indx.id
inner join sysindexkeys indk on indx.indid = indk.indid
inner join syscolumns col on obj.id = col.id and indk.colid = col.colid
where obj.xtype ='U' --AND col.name like '% %'
declare @id int
declare @tablename varchar(500)
select @id = 1
WHILE (@id <= (SELECT MAX(ID) FROM #tmp ))
BEGIN
SELECT @tablename =tablename FROM #tmp WHERE ID = @id
insert into #tt(index_name,index_Description,index_Keys)
exec sys.sp_helpindex @tablename
update #tt set tablename = @tablename where tablename is null
SELECT @id =@id+1
END
select * from #tt order by 1
drop table #tt
drop table #tmp
Comments please....
Srihari Nandamuri
January 27, 2009 at 8:54 am
You should check out the Scripts section of this site.
http://www.sqlservercentral.com/scripts/Index+Management/31652/
This particular one scripts the indexes out as a create. I find it to be a more useful type of output.
January 27, 2009 at 9:10 am
srihari nandamuri (1/27/2009)
Comments please....
If you want to submit articles or scripts, please see the "Write for us" link in the menu on the left. That way anything you submit goes into the site's article or script section.
The forums are for questions or discussions, not publications.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2009 at 9:20 am
In addition you are using SQL 2000 "compatibility" views you are posting on a SQL 2005 forum!
DMV are supposed to be used instead.
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply