查询出库中所有存在数据的表,并分别统计有多少条记录(较适合数据分析,优化等时候)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_SelectAllTables_Test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_SelectAllTables_Test]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
------------------------------------
--用途:查询出数据库中有数据的表,并统计有多少张表
--项目名称:DEMO
--作者:刘览器 QQ:136685116
--说明:
--时间:2010-03-17 00:56:48
------------------------------------
CREATE PROCEDURE P_SelectAllTables_Test
AS
declare my_cursor cursor for
select t.name from sysobjects t where t.type='U'
declare @table varchar(200)
declare @str nvarchar(4000)
declare @k int
declare @num int
declare @rowsCount int
set @rowsCount=0
open my_cursor
fetch my_cursor into @table
--exec master..xp_cmdshell 'bcp "exec BestHotelTest.dbo.P_Test" queryout "d:\test.txt " -c -S -U -P '
while (@@FETCH_STATUS=0)
begin
if (@table<>'dtproperties')
begin
set @str='select @a=count(1) from '+@table;
exec sp_executesql @str,N'@a int output',@num output/**********动态输出EXEC返回的结果***************/
select @k=@num
if(@k>0)
begin
print @table +'表有:' +LTRIM(str(@k)) +'条数据!'
print '---------------------------------'
set @rowsCount=@rowsCount+1
end
end
fetch my_cursor into @table
end
print '共计:' +str(@rowsCount,LEN(@rowsCount))+'张表有数据!'
close my_cursor
deallocate my_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO