积极答复者
关于游标中参数使用的问题

问题
-
问题是这样子的,数据库中有多个表含有IP地址信息,我想统计每个表中间,含有非零IP的记录数占整个表记录的百分比。
我的想法是这样,用游标在sys.tables中将需要的表名一个一个提取出来,然后计算每个表中含有IP地址的条数的百分比并输出。
单个表的百分比我可以用以下脚本计算出百分比:
declare @IP numeric(12,2)
declare @rows numeric(12,2)
declare @para nvarchar(100)
set @IP = (select count(0) as num from table_name where IP >0)
set @rows = (select rows from sys.sysindexes where id = object_id('table_name') and indid in (0,1))
set @para = (select convert(numeric(4,2),@IP / @rows*100) )
print @para
但是在游标中我无法用类似@table的变量替代table_name
不知道各位有什么好方法可以完成这个功能,多谢指导~~~~Thx!
凡人有庸俗的快乐,智者有高尚的痛苦
答案
-
CREATE TABLE #re( object_name sysname, ip int, rows int ); INSERT #re EXEC sp_msforeachtable @command1 = N' SELECT object_name = N''?'', ip = ( select count(0) as num from ? where IP > 0 ), rows = ( select rows from sys.sysindexes where id = object_id(N''?'') and indid in (0,1)) ) ', @whereand = N' AND EXISTS( SELECT * FROM syscolumns COL WHERE COL.id = O.id AND COL.name = N''IP'' ) '; SELECT *, para = (select convert(numeric(4,2),IP / rows*100) ) FROM #re
- 已标记为答案 Vincent-Z 2010年8月20日 1:49
全部回复
-
CREATE TABLE t1(ip numeric(12,2)) CREATE TABLE t2(ip numeric(12,2)) CREATE TABLE t3(noip numeric(12,2)) GO INSERT t1 SELECT 2 UNION ALL SELECT 0 UNION ALL SELECT 3 INSERT t2 SELECT 4 UNION ALL SELECT 0 UNION ALL SELECT 0 INSERT t3 SELECT 1 GO DECLARE @s VARCHAR(8000) SET @s=''; SELECT @s=@s+','+object_name(object_id) FROM sys.columns c WHERE name='ip'; SET @s=replace(@s,',',' SELECT CAST((sum(CASE WHEN ip>0 THEN 1 End)*1.0/count(*)*100) AS DECIMAL(18,2)) as 输出百分比 from ') exec (@s)
-
CREATE TABLE #re( object_name sysname, ip int, rows int ); INSERT #re EXEC sp_msforeachtable @command1 = N' SELECT object_name = N''?'', ip = ( select count(0) as num from ? where IP > 0 ), rows = ( select rows from sys.sysindexes where id = object_id(N''?'') and indid in (0,1)) ) ', @whereand = N' AND EXISTS( SELECT * FROM syscolumns COL WHERE COL.id = O.id AND COL.name = N''IP'' ) '; SELECT *, para = (select convert(numeric(4,2),IP / rows*100) ) FROM #re
- 已标记为答案 Vincent-Z 2010年8月20日 1:49