RSS

Script : index Information by Using DMV and INDEXPROPERTY Option

19 May

you can use this DMV Script to Show Index information in your database

SELECT obj.name,
ind.name,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexFillFactor’),0) [fill_factor],
create_date, modify_date, ind.type_desc,
fill_factor, has_filter,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexDepth’),0) [IndexDepth],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsAutoStatistics’),0) [IsAutoStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsStatistics’),0) [IsStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsUnique’),0) [IsUnique],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsClustered’),0) [IsClustered]
FROM sys.objects obj
INNER JOIN sys.indexes ind
ON obj.object_id = ind.object_id
WHERE obj.type = ‘U’
—and obj.name = ‘Computed_Cal’ —where you need to Show index info in Specfice table
–And ind.type_desc = ‘Heap’ — Where you need the Tables not have any Indexes

 
2 Comments

Posted by on May 19, 2013 in Index, Script

 

2 responses to “Script : index Information by Using DMV and INDEXPROPERTY Option

  1. Noreen

    March 14, 2014 at 10:24 PM

    Great blog here! Also your website loads up very fast!
    What host are you using? Can I get your affiliate link
    to your host? I wish my site loaded up as fast as yours lol

     
  2. http://containerking.co.za/

    March 24, 2014 at 10:32 PM

    Hello juyst wanted to give you a quick heads up. The words in your article seem to
    be running off the screen in Opera. I’m not sure iff this is a format issue or something to do with web browser compatibility but I thought
    I’d post to let you know. The layout look great though!

    Hoope you get the problem fixed soon. Many thanks

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s