SSMS2012查询所有表中的记录数（动态Select结果赋值变量）

这个其实通过系统表，一条语句就解决了

select a.name as 表名,max(b.rows) as 记录条数 
from sysobjects a,sysindexes b
where a.id=b.id and a.xtype='u'
group by a.name
order by a.name

 

-------------------------------------------------------- 另一种愚蠢的方法，主要在于使用exec sp_exectesql（）在存储过程中进行函数赋值运用

 

其实查询数据表中的记录数很简单，只需要使用count函数即可。

主要的难点在于编写存储过程、表变量使用，动态Select语句赋值。

这其中前两点基本已经掌握。本次主要记录动态Select语句赋值的问题。

首先，存储过程中如果想实现动态Select语句赋值变量，使用exec sp_exectesql（）是我目前掌握的唯一方法（如果有其他快捷方法也希望大家指导一下）。

 

sp_executesql官方语法如下：

sp_executesql [ @statement = ] statement
[ 
    { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } 
     { , [ @param1 = ] 'value1' [ ,...n ] }
]

@statement表示：待执行的Sql语句

@params表示：@statement中、即待执行SQL语句中，包含的所有参数定义的字符串，必须由N开头。由参数名、数据类型组成。通过out、output标识输出参数，表示顺序必须与Sql语句中的使用顺序相同。

@param1表示：参数字符串中第一个参数的值，后面以此类推

 

其中主要困惑我的就是sql语句中的参数值，既有动态获取的表值参数@tempname，也有待用的数量参数@countj。哪些需要标识在@params中，哪些不需要。

 

最开始编写代码为

    set @sqlStr =  'select @countj=count(*) from @tempname'
    exec sp_executesql @sqlStr,N'@countj bigint output,@tempname',@countj output,@tempname=@tempname

会报错：必须声明表变量@tempname。

因为表变量的特殊性，所以将代码改为：

    set @sqlStr =  'select @countj=count(*) from '+@tempname
    exec sp_executesql @sqlStr,N'@countj bigint output',@countj output

就可以正确施行。

这个参数的声明与赋值，可以在外部变量中先处理好再执行sp_executesql.这样就不需在@params、@param1中声明，赋值。如下变量@tempname、@aa都没有在sp_executesql声明

    declare @aa nvarchar(300)
    set @aa = 'xxx'

    set @sqlStr = 'select @countj=count(*) from '+@tempname+' where name = '''+@aa+''''
    exec sp_executesql @sqlStr,N'@countj bigint output',@countj output

也可以直接在sp_executesql中赋值。这是就需要在@params、@param1中声明、赋值。

    set @sqlStr = 'select @countj=count(*) from '+@tempname+' where name = @aa'
    exec sp_executesql @sqlStr,N'@countj bigint output,@aa nvarchar(300)',@countj output,@aa='xxx'

变量赋值可来自其他外部定义变量，sql语句内的变量名可以与外部变量名相同，不会冲突。

    declare @aa nvarchar(300)
    set @aa = 'xxx'

    set @sqlStr = 'select @countj=count(*) from '+@tempname+' where name = @aa'
    exec sp_executesql @sqlStr,N'@countj bigint output,@aa nvarchar(300)',@countj output,@aa=@aa




    declare @bb nvarchar(300)
    set @bb = 'xxx'

    set @sqlStr = 'select @countj=count(*) from '+@tempname+' where name = @aa'
    exec sp_executesql @sqlStr,N'@countj bigint output,@aa nvarchar(300)',@countj output,@aa=@bb

 

完整可行代码如下：

--创建临时表
create table #temptb (id int identity(1,1),tableName nvarchar(600),num bigint)

--执行存储过程
declare @sqlStr nvarchar(500)
declare @countj bigint
declare @tempname char(300)
declare tablename cursor for select name from sysobjects where xtype='U' order by name
open tablename
fetch next from tablename into @tempname
while(@@fetch_status <>-1)
begin
    set @countj = 0 

	set @sqlStr =  'select @countj=count(*) from '+@tempname
	exec sp_executesql @sqlStr,N'@countj bigint output',@countj output

	insert into #temptb (tableName,num) values(@tempname,@countj)

fetch next from tablename into @tempname
end
close tablename
deallocate tablename

--查询结果
select * from #temptb order by tableName
--删除查询结果
delete from #temptb