2010. 8. 19. 09:19
MSSQL
SQL Server 관리 시에 sysobjects 를 많이 이용하는데, 다음 두 가지 Procedure를 배우게 되면, 아주 쉽게 관리할 수 있겠더군요.
sp_MSforeachtable : 현재 사용중인 DB의 모든 table을 열거합니다.
예 : EXEC sp_MSforeachtable 'DBCC CLEANTABLE(''Northwind'', ''?'')'
sp_MSforeachdb : 현재 연결된 DB서버의 모든 Database를 열거합니다.
예 : EXEC sp_MSforeachdb 'DBCC CHECKDB (''?'')'
Here is my code for getting the row counts for each table in a database using a CURSOR:
use pubs
go
set nocount on
declare @cnt int
declare @table varchar(128)
declare @cmd varchar(500)
create table #rowcount (tablename varchar(128), rowcnt int)
declare tables cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open tables fetch next from tables into @table
while @@fetch_status = 0
begin
set @cmd = 'select ''' + @table + ''', count(*) from ' + @table
insert into #rowcount exec (@cmd)
fetch next from tables into @table
end CLOSE tables
DEALLOCATE tables
select top 5 * from #rowcount
order by tablename
drop table #rowcount
Here is the output of my CURSOR example when the above code in run on my machine:
tablename rowcnt ------------- ----------- authors 23 discounts 3 employee 43 jobs 8 pub_info 8
Now here is my code that produces similar results using the undocumented SP_MSforeachtable":
use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount
order by tablename
drop table #rowcount
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount
order by tablename
drop table #rowcount
Here is the output from the above code when run on my machine:
tablename rowcnt
----------------- -----------
[dbo].[authors] 23
[dbo].[discounts 3
[dbo].[employee] 43
[dbo].[jobs] 14
[dbo].[pub_info] 8
----------------- -----------
[dbo].[authors] 23
[dbo].[discounts 3
[dbo].[employee] 43
[dbo].[jobs] 14
[dbo].[pub_info] 8
