데이터가 없는 테이블 찾는 stored procedure

백업받은거 다 날렸네..

날짜보니 만든지 한참 됐네

혹시나 담에 사용할까봐서.. 블로깅..

MS-SQL 2000 을 다시 만지게 될까?

-- author  : aircook
-- date : 2004-06-30
CREATE PROCEDURE [dbo].[usp_SelectNotEmptyTable] AS

declare @table_name varchar(100)
declare @execstr varchar(5000)

create table #temp(
table_qualifiler varchar(50),
table_owner varchar(50),
table_name varchar(50),
table_type varchar(50),
remarks varchar(50)
)

create table #Temp2(
data_no int identity(1,1),
data_db varchar(50),
data_owner varchar(50),
data_table varchar(50)
)

insert into #temp
exec sp_tables

declare tempcursor cursor for

select table_name  from #temp
where table_type ='table'
 
open tempcursor
fetch next from tempcursor into  @table_name
 
while @@fetch_status = 0
begin
 
set @execstr='if exists(select top 1 * from '+@table_name+')
insert into #temp2 select table_qualifiler, table_owner, table_name  from #temp
where table_name = '''+@table_name +''''
-- print  @execstr
exec (@execstr)
fetch next from tempcursor into  @table_name
 
end
 
close tempcursor
 
deallocate tempcursor

select * from #temp2

drop table #temp
drop table #temp2

GO