帮我写个SQL程序,实现如下功能,本人想了很久,未果!
来源:百度知道 编辑:UC知道 时间:2024/06/05 14:52:57
2.功能:写一个存储过程,一运行,就可以把每个表的记录总数列出来。
3.具体要求:
a.要对每个数据库都找出来
b.只找每个数据库的用户表就行
c.结果要四列,名别为:数据库名,表名,未去重总条数,去重后总条数
d.按列“去重后总条数”进行降序排列
再次感谢!
对上面的改进了一下下:
CREATE procedure alltablecount
@DATABASE VARCHAR(100)
as
declare @name varchar(100)
EXEC('declare tablecur cursor for select name from '+@DATABASE+'.DBO.sysobjects where xtype= ''u ''' ) --
open tablecur
fetch next from tablecur into @name
while @@fetch_status!=-1
begin
exec ('insert into tablecount select '''+@DATABASE+''' ,'''+@name+''',count(1), (SELECT COUNT(1) FROM (SELECT DISTINCT * FROM '+@DATABASE+'.DBO.'+@name+' ) A) from '+@DATABASE+'.DBO.'+@name+' ')
fetch next from tablecur into @name
end
close tablecur
deallocate tablecur
select * from #tablecount order by reccount desc
go
create procedure alldatabasetotable as
if not exists (select * from sysobjects where name ='tablecount' )
create table tablecount(数据库名 VARCHA