/*
exec admin.dbo.indexesByFileGroup cache
*/
use admin
drop procedure dbo.indexesByFileGroup
go
create procedure dbo.indexesByFileGroup
	@dbName nvarchar(30)
as
/*******************************************************************************************************
*	dbo.indexesByFileGroup
*	Creator: Bill Wunder	
*	Date: 	4/29/2002
*	Project: 	
*	Project Mgr:	
*	Dev Contact:	
*	Outline:	list all indexes in a specified database by filegroup
*			This will aid in maintaing all nonclustered indexes on the INDEXES filegroup
*			
*			
*	How it Works:	
*			
*	usage:		EXECUTE admin.dbo.indexesByFileGroup alerts
*	Notes:		If everything is good the nonclustered indexes should all be at the top
*			(file group order - INDEXES comes before PRIMARY)
*	Modifications   
*                       
********************************************************************************************************/
declare @sql nvarchar(4000)

set @sql = 'select cast(o.name + ''.'' + i.name as varchar(70)) as [Table Name.Index Name], 
cast(min(v.name) as varchar(15)) as [Index Type], 
cast(g.groupname as varchar(10)) as [File Group],
i.rowcnt
from ' + @dbName + '.dbo.sysindexes i
inner join ' + @dbName + '.dbo.sysobjects o
on i.id = o.id
inner join master.dbo.spt_values v
on i.status & v.number = v.number
inner join ' + @dbName + '.dbo.sysfilegroups g
on i.groupid = g.groupid
where v.type = ''I'' 
and i.id > 100
and i.name not like ''_WA_Sys%''
and v.Name <> ''INDEX TYPES''
and i.indid > 0
and i.indid < 255
and o.uid = 1
and v.name in (''clustered'',''nonclustered'')
group by o.name, i.name, g.groupname, i.status, i.rowcnt
order by g.groupname, o.name, i.name, i.status, i.rowcnt'

exec sp_executesql @sql