麻烦高手改下代码。非常感谢解决方案

2012-03-24

麻烦高手改下代码。非常感谢代码作用描述：依次得出某张表中所有字段非NULL的有效记录数。代码可以执行，但

麻烦高手改下代码。非常感谢

代码作用描述：依次得出某张表中所有字段非NULL的有效记录数。代码可以执行，但是显示结果只有 COUNT出来的数字
我想要的结果是如下：
ID 9898
NAME 9723
XINGBIE 9898
NIANLIANG 7433
BEIZHU 1223

就是最后输出字段名有效记录数并且能现在在一个网格内。可以直接粘贴进 EXCEL。
代码如下：高手帮忙看下

[code=SQL][/code]
ALTER PROC [dbo].[cx_tab] (@tablename varchar(100))
AS
begin
set nocount on
declare @sqlstring varchar(200)
declare @table_col varchar(100)
declare cur_table cursor for
select name from syscolumns where id in (select id from sysobjects where name=@tablename)
open cur_table
fetch next from cur_table into @table_col
while @@fetch_status=0
begin
select @sqlstring ='select count(*) from '+@tablename+' where '+@table_col+' is not NULL '
exec (@sqlstring)

fetch next from cur_table into @table_col
--DEALLOCATE CUR_table
end
DEALLOCATE CUR_table
set nocount off
end
[code=SQL][/code]
我新手，我是做ORACLE的，SQLSERVER 2005环境，麻烦高手给改一下，我分不多。如果觉得分不够，请高手私下联系我

[解决办法]
不用游标行不？

SQL code

ALTER PROC [dbo].[cx_tab] (@tablename varchar(100)) AS declare @sql varchar(8000)select   @sql=isnull(@sql+' union all ','')  +'select '''   +name+''',count(1) as cnt from '+@tablename+' where '+name+' is not null'from syscolumns where id=object_id(@tablename)exec (@sql)go--exec cx_tab 'tb'
[解决办法]
SQL codeDECLARE @sql        VARCHAR(100)DECLARE @table      VARCHAR(100)SET @table = 'tb'--改为你的表名SET @sql = ''SELECT  @sql = @sql + ',COUNT(' + name + ') AS [' + name + ']' FROM sys.columns WHERE OBJECT_NAME(object_id) = @tableSELECT @sql = STUFF(@sql,1,1,'')SELECT @sqlEXEC ('SELECT ' + @sql + ' from ' + @table)

热点排行

SQL Server

麻烦高手改下代码。非常感谢解决方案