List and scripts of Indexes and clusters in SQL 2005 Database

  • 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

  • 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
  • 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.

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

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

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