-- Stored Procedure: dbo.TemporaryObjectsByDB -- 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].[TemporaryObjectsByDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[TemporaryObjectsByDB] GO create procedure dbo.TemporaryObjectsByDB as /******************************************************************************************************* * admin.dbo.TemporaryObjectsByDB * Creator: Bill Wunder * Date: 8-19-2002 * Project: * Project Mgr: * Dev Contact: * Outline: Generate a list of tables that seem to be temporary storage files that can be mailed out * to the group as a reminder. * * * usage: exec admin.dbo.TemporaryObjectsByDB * Notes: * * Modifications * ********************************************************************************************************/ declare @CrLf char(2) set nocount on create table #tables (id int identity(1,1) ,name nvarchar(128) ,DBName nvarchar(128)) create table #procedures (id int identity(1,1) ,name nvarchar(128) ,DBName nvarchar(128)) set @CrLf = char(13) + char(10) -- create a table that can be used to filter erroneous references from the email If object_id('admin.dbo.TemporaryObjectsByDBExcludeList','U') is null begin create table admin.dbo.TemporaryObjectsByDBExcludeList (DBName nvarchar(128) , ObjectName nvarchar(128) , RecCreatedDt datetime constraint dft_TemporaryObjectsByDBExcludeList__RecCreatedDt default getdate() , RecCreatedUser sysname constraint dft_TemporaryObjectsByDBExcludeList__RecCreatedUser default suser_sname()) exec sp_msforeachdb 'use ? if not exists (select 1 from admin.dbo.TemporaryObjectsByDBExcludeList where DBname = db_name() and ObjectName = ''dtproperties'') insert admin.dbo.TemporaryObjectsByDBExcludeList(DBName, ObjectName) values (db_name(),''dtproperties'')' insert admin.dbo.TemporaryObjectsByDBExcludeList(DBName, ObjectName) values (db_name(),'TemporaryObjectsByDBExcludeList') insert admin.dbo.TemporaryObjectsByDBExcludeList(DBName, ObjectName) values (db_name(),'TemporaryObjectsByDB') insert admin.dbo.TemporaryObjectsByDBExcludeList(DBName, ObjectName) values (db_name(),'TemporaryObjectsByDBEmailReport') end exec sp_msforeachdb 'use ? if ''?'' in (''master'',''model'') insert #tables(name, DBName) select user_name(uid) + ''.'' + name, db_name() from sysobjects where type = ''U'' and not exists(select 1 from admin.dbo.TemporaryObjectsByDBExcludeList where DBname = db_name() and ObjectName = name COLLATE SQL_Latin1_General_CP1_CI_AS) and NOT(left(name,3) = ''sys'') and NOT(left(name,3) = ''spt'') and NOT(left(name,2) = ''MS'') if ''?'' = ''DMAUpload'' insert #tables(name, DBName) select user_name(uid) + ''.'' + name, db_name() from sysobjects where type = ''U'' and not exists(select 1 from admin.dbo.TemporaryObjectsByDBExcludeList where DBname = db_name() and ObjectName = name COLLATE SQL_Latin1_General_CP1_CI_AS) and charindex(''raw'', name) = 0 and (charindex(''aaron'', name) > 0 or charindex(''apollo'', name) > 0 or charindex(''athena'', name) > 0 or charindex(''bad'', name) > 0 or charindex(''cindi'', name) > 0 or charindex(''demo'', name) > 0 or charindex(''deleted'', name) > 0 or charindex(''dup'', name) > 0 or charindex(''jjs'', name) > 0 or charindex(''jason'', name) > 0 or charindex(''john'', name) > 0 or charindex(''xx'', name) > 0 or charindex(''temp'', name) > 0 or charindex(''tmp'', name) > 0 or (charindex(''test'', name) > 0 and charindex(''latest'', name) = 0) or charindex(''trace'', name) > 0 or charindex(''zeus'', name) > 0 or charindex(''$'', name) > 0 or right(name,3) in (''bak'', ''old'',''sav'') or right(name,4) in (''back'') or isnumeric(right(name,5)) > 0 or name in (''foo'', ''oct29''))' exec sp_msforeachdb 'use ? if ''?'' not in (''DMAUpload'',''master'',''model'',''msdb'',''Northwind'',''pubs'',''tempdb'',''distributor'' ,''QDB'',''SolSystem'',''WallStreetEstate'',''WallStreetOnDemand'',''OnePoint'',''IMC'') insert #tables(name, DBName) select user_name(uid) + ''.'' + name, db_name() from sysobjects where type = ''U'' and not exists(select 1 from admin.dbo.TemporaryObjectsByDBExcludeList where DBname = db_name() and ObjectName = name COLLATE SQL_Latin1_General_CP1_CI_AS) and (charindex(''aaron'', name) > 0 or charindex(''apollo'', name) > 0 or charindex(''athena'', name) > 0 or charindex(''bad'', name) > 0 or charindex(''cindi'', name) > 0 or charindex(''demo'', name) > 0 or charindex(''deleted'', name) > 0 or charindex(''dup'', name) > 0 or charindex(''jjs'', name) > 0 or charindex(''jason'', name) > 0 or charindex(''john'', name) > 0 or (charindex(''xx'', name) > 0 and db_name() <> ''DataScope'') or charindex(''temp'', name) > 0 or charindex(''tmp'', name) > 0 or (charindex(''test'', name) > 0 and charindex(''latest'', name) = 0) or charindex(''trace'', name) > 0 or charindex(''zeus'', name) > 0 or (charindex(''$'', name) > 0 and db_name() <> ''DataScope'') or right(name,3) in (''bak'', ''old'',''sav'') or right(name,4) in (''back'') or isnumeric(right(name,5)) > 0 or name in (''foo'', ''oct29''))' exec sp_msforeachdb 'use ? if ''?'' not in (''DMAUpload'',''master'',''model'',''msdb'',''Northwind'',''pubs'',''tempdb'',''distributor'', ''QDB'',''SolSystem'',''WallStreetEstate'',''WallStreetOnDemand'',''OnePoint'',''IMC'') insert #procedures(name, DBName) select user_name(uid) + ''.'' + name, db_name() from sysobjects where type = ''P'' and not exists(select 1 from admin.dbo.TemporaryObjectsByDBExcludeList where DBname = db_name() and ObjectName = name COLLATE SQL_Latin1_General_CP1_CI_AS) and (charindex(''aaron'', name) > 0 or charindex(''apollo'', name) > 0 or charindex(''athena'', name) > 0 or charindex(''bad'', name) > 0 or charindex(''cindi'', name) > 0 or charindex(''deleted'', name) > 0 or charindex(''demo'', name) > 0 or charindex(''dup'', name) > 0 or charindex(''jjs'', name) > 0 or charindex(''jason'', name) > 0 or charindex(''john'', name) > 0 or charindex(''xx'', name) > 0 or charindex(''temp'', name) > 0 or charindex(''tmp'', name) > 0 or (charindex(''test'', name) > 0 and charindex(''latest'', name) = 0) or charindex(''trace'', name) > 0 or charindex(''zeus'', name) > 0 or charindex(''$'', name) > 0 or right(name,3) in (''bak'', ''old'',''sav'') or right(name,4) in (''back'') or isnumeric(right(name,5)) > 0 or name in (''foo'', ''oct29''))' select + ' Tables and Procedures that appear to have been temporarily left on server ' + @@servername + '.' + @CrLf + @CrLf + ' Please remove any you know to be temporary and are no longer needed or exclude from this report' + @CrLf + ' if is required by an application by executing the provided exclusion list insert statement.' + @CrLf + @CrLf union select ' /*' + @CrLf + ' drop table ' + DBName + '.' + name + @CrLf + ' */' + @CrLf + ' insert admin.dbo.TemporaryObjectsByDBExcludeList (DBName, ObjectName)' + @CrLf + ' values (''' + DBname + ''',''' + name + ''')' + @CrLf + @CrLf from #Tables union select ' /*' + @CrLf + ' use ' + DBName + ' drop procedure ' + name + @CrLf + ' */' + @CrLf + ' insert admin.dbo.TemporaryObjectsByDBExcludeList (DBName, ObjectName) ' + @CrLf + ' values (''' + DBname + ''',''' + name + ''')' + @CrLf + @CrLf from #Procedures GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO