自己写的
declare
@count
int
DECLARE @SQLString nvarchar ( 500 )
, @TableName varchar ( 100 )
SELECT top 1 @TableName = name from sysobjects where xtype = ' u '
SELECT @SQLString = ' select @innercount=count(1) from ' + @TableName
EXECUTE sp_executesql
@SQLString
,N ' @innercount int OUTPUT '
, @innercount = @count OUTPUT
select @TableName + ' 记录数: ' + CAST ( @count as varchar ( 20 ))
DECLARE @SQLString nvarchar ( 500 )
, @TableName varchar ( 100 )
SELECT top 1 @TableName = name from sysobjects where xtype = ' u '
SELECT @SQLString = ' select @innercount=count(1) from ' + @TableName
EXECUTE sp_executesql
@SQLString
,N ' @innercount int OUTPUT '
, @innercount = @count OUTPUT
select @TableName + ' 记录数: ' + CAST ( @count as varchar ( 20 ))
网上的:
declare
@name
varchar
(
200
)
declare @i int
declare @result table (name varchar ( 100 ))
declare @s nvarchar ( 100 )
declare A cursor for ( SELECT name FROM sysobjects where type = ' U ' ) OPEN A
fetch next from A into @name
WHILE @@FETCH_STATUS = 0
begin
set @s = ' select top 10 @i=COUNT(1) from ' + @name + ''
exec sp_executesql @s ,N ' @i int output ' , @i
output
if @i > 0
insert into @result select @name
fetch next from A into @name
end
close A
deallocate A
select * from @result
declare @i int
declare @result table (name varchar ( 100 ))
declare @s nvarchar ( 100 )
declare A cursor for ( SELECT name FROM sysobjects where type = ' U ' ) OPEN A
fetch next from A into @name
WHILE @@FETCH_STATUS = 0
begin
set @s = ' select top 10 @i=COUNT(1) from ' + @name + ''
exec sp_executesql @s ,N ' @i int output ' , @i
output
if @i > 0
insert into @result select @name
fetch next from A into @name
end
close A
deallocate A
select * from @result
