Technical Article

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)