[MSSQL] 반복문을 통한 특정 단어 포함한 테이블 조회, 특정 테이블을 사용하는 프로시저 조회

가끔 업무를 진행하다보면

mssql 서버 내 전체 DB 대상으로 특정단어를 포함하고 있는 테이블이나 

특정 테이블을 사용하는 프로시저를 조회할 때가 많습니다.

 

매번 DB 연결을 변경하며 INFORMATION_SCHEMA.TABLES, sys.objects 를 조회하는 건 비효율적이므로

전체 DB 대상으로 반복문을 통해 조회하는 방식으로 작업하고 있습니다.

 

개인적인 작업용도로 작성한 터라 부족한 부분이 있지만 공유합니다.

--dblist 조회
select name as db_name, ROW_NUMBER() over(order by name) as rn
  into #db_list
  from sys.sysdatabases

--1) 특정 단어를 포함한 테이블 조사  
create table #tmp_tab_list(create_date datetime, modify_date datetime, db_nm varchar(30), tab_nm varchar(1000))

declare @i		int
declare @dbnm	varchar(30)
declare @qry1	nvarchar(max)

set @i = 1
DECLARE @spName NVARCHAR(MAX)

while @i < (select max(rn)+1 from #db_list)
begin
	select @dbnm = db_name from #db_list where rn = @i
	set @qry1 = '
					insert into #tmp_tab_list
					SELECT b.create_date, 
					       b.modify_date,
						   a.TABLE_CATALOG as db_nm,
						   upper(a.TABLE_NAME) as table_name
						FROM '+@dbnm+'.INFORMATION_SCHEMA.TABLES a 
						inner join '+@dbnm+'.sys.objects b on b.name = a.TABLE_NAME
						where upper(a.TABLE_NAME) like ''XTMP%''  
					 
				'
	exec(@qry1)
	set @i = @i+1
end

select * from #tmp_tab_list


--2) 특정 테이블 사용중인 sp조회
create table #tmp_tab_use_list(db_nm varchar(30), tab_nm varchar(1000), sp_name varchar(1000) )

declare @i		int
declare @dbnm	varchar(30)
declare @qry1	nvarchar(max)

set @i = 1
DECLARE @spName NVARCHAR(MAX)

while @i < (select max(rn)+1 from #db_list)
begin
	select @dbnm = db_name from #db_list where rn = @i
	set @qry1 = '
					insert into #tmp_tab_use_list					
					select '''+@dbnm+''' as db_name,t.tab_nm, o.name as sp_name
					  from '+@dbnm+'.dbo.syscomments c with(nolock)  
					inner join '+@dbnm+'.dbo.sysobjects o with(nolock) on o.id = c.id
					inner join #tmp_tab_list t on upper(c.text) like ''%''+tab_nm+''%''
					where db_nm = '''+@dbnm+'''
					  and t.tab_nm like ''%xtmp_test%''
	
				'
	exec(@qry1)
	set @i = @i+1
end

select * from #tmp_tab_use_list