{"id":66,"date":"2008-10-02T12:04:06","date_gmt":"2008-10-02T17:04:06","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=66"},"modified":"2012-07-30T21:17:05","modified_gmt":"2012-07-31T03:17:05","slug":"t-sql-create-view-to-look-at-datbase-indexes","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=66","title":{"rendered":"T-SQL – Create View to look at Database Indexes"},"content":{"rendered":"

Wrote a simple query to look at basic attributes of indexing in a specific database:<\/p>\n

SELECT\u00a0<\/span><\/span><\/span>\u00a0\u00a0 [IndexGroup]<\/p>\n

<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,<\/span><\/span>[FileName]<\/p>\n

<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,<\/span><\/span>[TableName]<\/p>\n

<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,<\/span>[IndexName]<\/p>\n

<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,<\/span><\/span>[dpages]<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,<\/span><\/span>[rowcnt]<\/p>\n

<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,<\/span><\/span>[rows]<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,<\/span><\/span>[OrigFillFactor]<\/p>\n

<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,<\/span><\/span>[groupid]<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,<\/span><\/span>[allocpolicy]<\/p>\n

<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,<\/span><\/span>[status]<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,<\/span><\/span>[groupname]<\/p>\n

<\/span>FROM<\/span><\/span> [dbo]<\/span>.<\/span><\/span>[INDEX_INFORMATION]<\/span><\/p>\n

[Click Below to get the complete code used to create the database view]<\/strong><\/p>\n

<\/p>\n

USE [YOUR DATABASENAME]\r\nGO\r\nIF OBJECT_ID('dbo.INDEX_INFORMATION ','v') IS NOT NULL\r\nBEGIN\r\n\tDROP VIEW dbo.INDEX_INFORMATION\r\nEND \r\n\r\nGO\r\nCREATE VIEW dbo.INDEX_INFORMATION\r\nAS\r\n\/*\r\n\tProvides Basic Index Information\r\n\tto look up INDEX information\r\n\tJCD 10\/2\/2008\r\n*\/\r\n\r\nSelect\r\n\t CONVERT(VARCHAR(40),lower(d.groupname)) as [IndexGroup]\r\n\t,CONVERT(VARCHAR(40),lower(c.name)) as [FileName]\r\n\t,CONVERT(VARCHAR(40),UPPER(a.Name)) as TableName\r\n\t,CONVERT(VARCHAR(40),lower(b.name)) as IndexName\r\n\t,b.dpages\r\n\t,b.rowcnt\r\n    ,b.rows\r\n\t,b.OrigFillFactor\r\n,d.*\r\n\r\nfrom sysobjects as a\r\nJOIN sys.sysindexes as b\r\n\ton a.id=b.id\r\nJOIN sys.sysfiles as c\r\n\ton c.groupid=b.groupid\r\nJOIN sys.sysfilegroups as d\r\n\ton c.groupid=d.groupid\r\nwhere (a.type='u'\r\n\tand b.name IS NOT NULL\r\n\tand b.name NOT like '_WA_sys_%')\r\n\r\nGO<\/pre>\n","protected":false},"excerpt":{"rendered":"
Basic view makes looking up Index attributes such as size group table names a bit easier.. <\/p>\n
Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[138,5],"tags":[36,247,246,291,245],"class_list":["post-66","post","type-post","status-publish","format-standard","hentry","category-programming","category-t-sql","tag-sql","tag-sql-server-2005","tag-syntax","tag-t-sql","tag-view"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/66","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=66"}],"version-history":[{"count":5,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/66\/revisions"}],"predecessor-version":[{"id":68,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/66\/revisions\/68"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=66"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=66"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=66"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}