Master of None

Wrote a simple query to look at basic attributes of indexing in a specific database:

SELECT    [IndexGroup]

               ,[FileName]

               ,[TableName]

               ,[IndexName]

               ,[dpages]

               ,[rowcnt]

               ,[rows]

               ,[OrigFillFactor]

               ,[groupid]

               ,[allocpolicy]

               ,[status]

               ,[groupname]

FROM [dbo].[INDEX_INFORMATION]

[Click Below to get the complete code used to create the database view]

USE [YOUR DATABASENAME]
GO
IF OBJECT_ID('dbo.INDEX_INFORMATION ','v') IS NOT NULL
BEGIN
	DROP VIEW dbo.INDEX_INFORMATION
END 

GO
CREATE VIEW dbo.INDEX_INFORMATION
AS
/*
	Provides Basic Index Information
	to look up INDEX information
	JCD 10/2/2008
*/

Select
	 CONVERT(VARCHAR(40),lower(d.groupname)) as [IndexGroup]
	,CONVERT(VARCHAR(40),lower(c.name)) as [FileName]
	,CONVERT(VARCHAR(40),UPPER(a.Name)) as TableName
	,CONVERT(VARCHAR(40),lower(b.name)) as IndexName
	,b.dpages
	,b.rowcnt
    ,b.rows
	,b.OrigFillFactor
,d.*

from sysobjects as a
JOIN sys.sysindexes as b
	on a.id=b.id
JOIN sys.sysfiles as c
	on c.groupid=b.groupid
JOIN sys.sysfilegroups as d
	on c.groupid=d.groupid
where (a.type='u'
	and b.name IS NOT NULL
	and b.name NOT like '_WA_sys_%')

GO

Tiny URL for this post:
 Copy