-- Stored Procedure: dbo.IndexesOnWrongFileGroupEmail
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexesOnWrongFileGroupEmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[IndexesOnWrongFileGroupEmail]
GO




create procedure dbo.IndexesOnWrongFileGroupEmail
 @recipients varchar(100) 
as
If object_id('admin.dbo.FilegroupEmailList','U') is null  
 create table admin.dbo.FilegroupEmailList ([Indexes on Wrong FileGroup] varchar(150))
Else
 truncate table admin.dbo.FilegroupEmailList

insert admin.dbo.FilegroupEmailList
exec sp_msforeachdb 'use ? 
if ''?'' not in (''master'',''model'',''msdb'',''tempdb'',''admin'')
and exists (select * from sysfiles where groupid > 1 and name like ''%index%'')
and exists (select 1 from sysindexes i
  where i.id > 100 
  and i.name not like ''_WA_%''
  and object_name(i.id) not like ''sys%''
  and object_name(i.id) not like ''_trace%''
   and (((i.indid in (0,1) and i.groupid > 1)
  or ((i.indid between 2 and 249) and i.groupid = 1)))) 
 begin                
  select  cast(''['' + db_name() + ''].['' + user_name(objectproperty(i.id,''OwnerId'')) + ''].['' 
    + object_name(i.id) 
    + ''].['' + i.name + ''] -- indid '' 
    + cast(indid as varchar(6)) + '' File Group ''
    + cast((select top 1 groupname 
      from sysfilegroups 
      where groupid = i.groupid) as varchar(10)) 
  as varchar(133)) 
  from sysindexes i
  where i.id > 100 
  and i.name not like ''_WA_%''
  and object_name(i.id) not like ''sys%''
  and object_name(i.id) not like ''_trace%''
   and (((i.indid in (0,1) and i.groupid > 1)
  or ((i.indid between 2 and 249) and i.groupid = 1)))
 end'
if exists (select 1 from admin.dbo.FilegroupEmailList) 
 exec sysmon.dbo.safe_sendmail @recipients = @recipients,
         @subject = 'Indexes on Wrong FileGroup',
                              @message = 'Please review the attached list. Correct the SourceSafe script and the Database for any indexes that you have created incorrectly.',
         @query = 'set nocount on select [Indexes on Wrong FileGroup] from admin.dbo.FilegroupEmailList',
         @width = 151  
             





GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO