先,谢谢。我看了一下sp_executesql 帮助,可是…………,还是
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO-- =============================================
-- ALTER procedure basic template
-- =============================================
-- creating the store procedure
create PROCEDURE proc_park_table_test
AS
declare @tablename char(50)
declare @cou int
DECLARE obj CURSOR FOR
select name from sysobjects where xtype='u'open obj
FETCH next FROM obj into @tablenameprint @tablenameWHILE @@FETCH_STATUS = 0
BEGIN
exec('select @cou= count(*) from '+@tablename)--跑不起来if not exists(select * from park_test where tablename = @tablename)
begin
insert into park_test values(@tablename,@cou,0)
--取的当前的表的记录行数
end
else
begin
update park_test set newcount=@cou,oldcount= newcount where tablename=@tablename
--取当前行数,换上一次的行数
end
FETCH NEXT FROM obj into @tablename
print @tablename
END
CLOSE obj
DEALLOCATE obj
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO-- =============================================
-- ALTER procedure basic template
-- =============================================
-- creating the store procedure
create PROCEDURE proc_park_table_test
AS
declare @tablename char(50)
declare @cou int
DECLARE obj CURSOR FOR
select name from sysobjects where xtype='u'open obj
FETCH next FROM obj into @tablenameprint @tablenameWHILE @@FETCH_STATUS = 0
BEGIN
exec('select @cou= count(*) from '+@tablename)--跑不起来if not exists(select * from park_test where tablename = @tablename)
begin
insert into park_test values(@tablename,@cou,0)
--取的当前的表的记录行数
end
else
begin
update park_test set newcount=@cou,oldcount= newcount where tablename=@tablename
--取当前行数,换上一次的行数
end
FETCH NEXT FROM obj into @tablename
print @tablename
END
CLOSE obj
DEALLOCATE obj
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
declare @cou int
select @tablename='authors'--以下可以修改
--以下为@cou 中的值。select @cou=count(*) from authorsexec('select * from '+ @tablename)
set @cou = @@rowcountprint convert(char,@cou)
--declare @cou int
select @tablename='authors'--以下可以修改
--以下为@cou 中的值。select @cou=count(*) from authors
declare @sql char(100)
set @sql = 'declare @cou int select @cou=count(*) from '+ @tablename
exec(@sql)
不过还有点小问题:
exec('select * from '+ @tablename)
会产生一集果集。能否去掉。set @sql = 'declare @cou int select @cou=count(*) from '+ @tablename
的变量@cou能否取出来不胜感激
会产生一集果集。能否去掉。=======================
你不要结果集那用*做什么啊?有点不明白?请说明你的真正意思
set @sql = 'declare @cou int select @cou=count(*) from '+ @tablename
的变量@cou能否取出来===================
当然可以啦,用临时表就是一个最简单的方法
或是用这个过程:sp_spaceused显示行数、保留的磁盘空间以及当前数据库中的表所使用的磁盘空间,或显示由整个数据库保留和使用的磁盘空间。sp_spaceused [[@objname =] 'objname'][,[@updateusage =] 'updateusage']
EXEC sp_spaceused 'titles'
这是个不错的方法:不过如何取得他的Rows结果18放到@cou变量呢?? gmlxf(烛光) :高手你好,我的水平可能还很底,能不能说xian
GO
INSERT #tmp EXEC sp_spaceused 'pubs..authors'
SELECT [rows] FROM #tmp
DROP TABLE #tmp
select rows from sysindexes where id = object_id('pubs..authors') and indid in (0,1)
--不可修改,use pubs
declare @tablename char(50)
declare @cou int
declare @sql nvarchar(4000)
select @tablename='authors'--以下可以修改
--以下为@cou 中的值。select @cou=count(*) from authors
select @sql = N'select @cou=count(*) from '+ @tablenameexec sp_executesql @sql,N'@cou int output',@cou outputprint @cou--以上程序可以直接贴在SQL中调试
drop table #d
create table #d (name char(120),
rows int null,
reserved char(120) null,
data char(120) null,
indexp char(120) null,
unused char(120) null)
insert #d
exec sp_spaceused 'titles'select * from #d/*(所影响的行数为 1 行)name rows reserved data indexp unused
------------------------------------------------------------------------------------------------------------------------ ----------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------
titles 18 40 KB 8 KB 32 KB 0 KB (所影响的行数为 1 行)
*/
这里在来领分