List and scripts of Indexes and clusters in SQL 2005 Database

Question

List and scripts of Indexes and clusters in SQL 2005 Database

go2vish

Old Hand

Points: 333
More actions
September 24, 2008 at 4:08 am

#382794

Hi There,
Can anyone of you please help. I need to list down the List and scripts of Indexes and clusters in SQL 2005 Database.
I need to find then in order like
[font="Courier New"]Table Name, Index name, Index Script, Name of columns, Clustered or Non-Clustered. [/font]
Please reply!!!
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jerry Hung SSChampion Points: 12968 More actions · Answer 1

I've collected many index scripts over the time, don't know which one exactly fits, but this one does everything but the CREATION SCRIPT

--Create Procedure SQL_IndeXUsage as

Set NOCOUNT ON

------------------------------------------------

-- Auhtor: Saleem Hakani (WWW.SQLCOMMUNITY.COM)

-- Date: June 24th 2007 @ 7:12 PM PST

-- Description: This procedure shows you index usage

-- Compatibility: SQL Server 2005 only

-- Disclaimer: This script, is provided for informational purposes only and SQL Server Community (aka: http://WWW.SQLCOMMUNITY.COM) or the author of this script makes no warranties,

-- either express or implied. This script, scenarios and other external web site references, is subject to change without notice.

-- The entire risk of the use or the results of the use of this script remains with the user.

-------------------------------------------------

If exists (Select name from sysobjects where name = '#TmpTable')

Begin

Drop Table #TmpTable

End

If not exists (Select name from sysobjects where name = '#TmpTable')

Begin

Create Table #TmpTable

(

Database_Name Varchar(255),

ObjectName Varchar(255),

Index_Name Varchar(255),

Index_Type Varchar(50),

Total_Reads Int,

Total_Writes Int

)

End

Declare @DBName Varchar(255),@Str NVarchar(4000)

SET @DBName=(SELECT MIN(Name) FROM Master.sys.Databases where Name not in ('Master','MSDB','Model','TempDB'))

WHILE @DBName IS NOT NULL

BEGIN

Select @STR='Use ['+@DBName+'];

Select '''+Ltrim(@DBName)+''' as ''Database_Name'',

Object_Name(a.Object_ID) as ''Object_Name'',

a.Name as ''Index_NAME'',

a.Type_Desc as ''Index_TYPE'',

(b.User_Seeks + b.User_Scans + User_Lookups) as ''Total_Reads'',

b.User_Updates as ''Total_Writes''

From

Sys.DM_DB_Index_Usage_Stats b join Sys.Indexes a

on a.Object_ID = b.Object_ID and

a.Index_ID = b.Index_ID and

b.Database_ID = DB_ID('''+@DBName+''') and

Object_Name(a.Object_ID) not like ''sys%''

and ObjectProperty(a.object_id,''IsUserTable'') = 1'

Insert Into #TmpTable Exec SP_ExecuteSQL @STR

SET @DBName=(SELECT MIN(Name) FROM Master.Sys.Databases where name > @DBName and Name not in ('Master','MSDB','Model','TempDB'))

END

Select * from #TmpTable

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Please don't cross post. There is an identical question here: http://www.sqlservercentral.com/Forums/FindPost575007.aspx

with one post in it as well.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

go2vish Old Hand Points: 333 More actions · Answer 3

Thanks a lot for the help:D. but could please help on - how to get the query for an existing Index?