How to Identify backup tables within production databases

,

This is a useful script to identify tables within your database(s) created by developers.

Possibly they have been created with a name that contains backup or Temp.

I developed this script recently to identify what tables could be removed, to return much needed space to a database server.

CREATE PROCEDURE dbo.usp_IdentifyArchiveTables
AS
BEGIN
	DECLARE @dbname VARCHAR(60)   
	DECLARE @dbid INT 
	DECLARE @execmd VARCHAR(5000)   
	DECLARE db CURSOR FAST_FORWARD FOR    

	SELECT dbid, DB_NAME(dbid) AS name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','TempDB', 'msdb', 'model','tempdb')   
	OPEN db    
		---2. Ensuring that the Cursor fetches data into the correct parameters
		FETCH NEXT FROM db INTO @dbid, @dbname 
		   WHILE @@Fetch_status=0 
			   BEGIN
				   SET @execmd='SELECT * FROM [' + @dbname +'].[INFORMATION_SCHEMA].[TABLES] WHERE Table_Name like ''%201%'' '
				   SET @execmd=@execmd+' UNION '+  'SELECT * FROM [' + @dbname +'].[INFORMATION_SCHEMA].[TABLES] WHERE Table_Name like ''%ClientConv%Backup%'''
				   SET @execmd=@execmd+' UNION '+  'SELECT * FROM [' + @dbname +'].[INFORMATION_SCHEMA].[TABLES] WHERE Table_Name like ''%Backup'''
				   SET @execmd=@execmd+' UNION '+  'SELECT * FROM [' + @dbname +'].[INFORMATION_SCHEMA].[TABLES] WHERE CHARINDEX(''TEMP'',Table_Name,0) <> 0'
				   print @execmd
				   EXEC (@execmd) 

				  --BREAK
				 FETCH NEXT FROM db INTO @dbid, @dbname  
				END
	CLOSE db    
	DEALLOCATE db
END

Rate

2.14 (7)

Share

Share

Rate

2.14 (7)