SQL Server 2005에서 사용하지 않는 테이블을 찾아 정리하는 방법

SQL Server 2005에서 사용하지 않는 테이블을 찾아 정리하는 방법

1.     sp_depends
아래 그림은 sp_depends 실행결과이다. tblCall10 번을 참조하는 프로시저들이 얼마나 있는지를 알 수 있다.

2.     sysobjects 와 syscomments
두 개의 시스템 테이블을 이용하여 tblCall10 이라는 텍스트를 가진 모든 프로시저들의 목록을 볼 수 있다. 많을수록 여러 군데에서 참조하고 있음을 알 수 있다.

SELECT         DISTINCT a.name FROM         sysobjects AS a         INNER JOIN syscomments  AS b ON a.id=b.id AND b.text LIKE '%tblCall10%'

3.     sp_depends 프로시저 응용 편
특수한 시스템 테이블 몇 개를 이용하여, 의존성 개수 및 총 Row를 한 눈에 파악하여 테이블 사용유무를 판별할 수 있다.

ALTER PROC usp_depends_ex_fast @dbname varchar(100) /**************************************         1. 작성자: 민경태         2. 작성일: 2011.10.17         3. 기 능: 현재DB에있는모든테이블의의존성과Row수를구한다.         의존성이0 이거나Row 수가0에가까운테이블은사용하지않을가능성이높다.         exec usp_depends_ex_fast 'budget' ***************************************/ AS SET NOCOUNT ON   DECLARE @sqlstring NVARCHAR(2000)       SET @sqlstring =' SELECT         t.table_catalog AS TBLCTLG         , t.table_schema AS TBLSCHM         , t.table_name AS TBLNAME         , COUNT(d.depid) AS DEPCNT         , i.rows AS ROWCNT FROM         '+@dbname+'.INFORMATION_SCHEMA.TABLES t         INNER JOIN '+@dbname+'..sysobjects AS a ON t.TABLE_NAME=a.NAME AND a.xtype=''U''         INNER JOIN '+@dbname+'..sysindexes as i ON a.id = i.id AND i.indid < 2         LEFT OUTER JOIN '+@dbname+'..sysdepends AS d ON a.id=d.depid GROUP BY t.table_catalog, t.table_schema, t.table_name, i.rows, d.depid ORDER BY t.table_catalog, t.table_schema, t.table_name ' EXEC sp_executesql @sqlstring

단, 위의 방법들은 SP 프로시저와 어느 정도 의존성을 가지고 있는가 하는 정도와 테이블이 비어있는지 확인하는 정도이지 100% 믿을 수 있는 데이터는 아니다. 가령 AD-HOC쿼리를 통해 사용되어지는 테이블의 경우에는 의존도는 0 이지만 데이터의 수는 많을 수 있다. 다른 경우로 예전에 사용하기 위해 SP들은 만들어 졌지만 어느 시점 이후로 전혀 사용되지 않은 테이블의 경우 의존도는 있을 수 있지만 데이터 수는 현격히 적을 수 있다. 그렇기 때문에 의존도와 Row수를 적절히 보고 판단하여 의심이 가는 테이블을 찾을 확률을 높이는데 도움이 될 것이다.

TIPS!

EXEC SP_MSFOREACHDB ' usp_depends_ex_fast ''?'' '

위와같이 SP_MSFOREACHDB SP를 사용하면 전체 DB에 대해서 한방에 실행 결과를 얻을 수 있다

.