use admin
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE dbo.FindHeaps
as
/*******************************************************************************************************
*	admin.dbo.FindHeaps
*	Creator:	Bill Wunder 
*
*       Date:           3-13-2003 
*
*	Project:	utility
*
*	Description:	identify all tables with no clustered index
*
*	Usage:
	     EXECUTE admin.dbo.FindHeaps
*       notes: proc will create it's tracking table if not already there. Run proc weekly from Agent
*
*	Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/

set nocount on
-- added line to see if profile recognizes an alter table
-- find all heap tables
if objectproperty(object_id('admin.dbo.heaps'),'IsUserTable') is null
	create table admin.dbo.heaps 
		(ServerName varchar(10), 
		DatabaseName varchar(30), 
		TableName varchar(50), 
		RowsInTable int, 
		RecMaintainedDt datetime, 
		constraint pk_heaps primary key clustered (TableName, DatabaseName, ServerName))
exec master.dbo.sp_msforeachdb 
	'use ? 
	declare @StartDt datetime
	select @StartDt = current_timestamp
	if (db_name() not in (''master'',''model'',''tempdb'',''msdb'',''pubs'',''NorthWind'',''admin''))
		begin
			update admin.dbo.heaps 
			set RowsInTable = i.rows,
			    RecMaintainedDt = @StartDt	
			from master.dbo.sysindexes i
			inner join admin.dbo.heaps h
			on i.id = object_id(h.TableName) 
			where i.indid = 0
			and h.ServerName = @@servername
			and h.DatabaseName = db_name()
			and OBJECTPROPERTY(i.id,''IsUserTable'') = 1

			insert admin.dbo.heaps (ServerName, DatabaseName, TableName, RowsInTable, RecMaintainedDt)	
			select @@servername, db_name(), object_name(id), rows, @StartDt
			from sysindexes i 
			where indid = 0
			and OBJECTPROPERTY(id,''IsUserTable'') = 1
			and not exists (select 1 from admin.dbo.heaps 
					where TableName = object_name(i.id) 
					and DatabaseName = db_name()
					and ServerName = @@servername)

			if exists (select 1 from admin.dbo.heaps 
					where RecMaintainedDt < @StartDt
					and ServerName = @@servername
					and DatabaseName = db_name())
				begin
					raiserror (''The following tables are no longer heaps on server %s in database %s.'',-1,-1, @@servername,''?'')
					select ServerName, 
						DatabaseName, 
						TableName, 
						RowsInTable, 
						RecMaintainedDt
					from admin.dbo.heaps 
					where RecMaintainedDt < @StartDt
					and ServerName = @@servername
					and DatabaseName = db_name()
			 
					delete admin.dbo.heaps 
					where RecMaintainedDt <> @StartDt
					and ServerName = @@servername
					and DatabaseName = db_name()	
				end
		end'

print 'admin.dbo.heaps has been updated with current heap info'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO