declare @tablename varchar(100) declare @sql varchar(1000) declare cur cursor for select name from sysobjects where type='u' open cur FETCH NEXT FROM cur INTO @tablename while @@FETCH_STATUS = 0 begin set @sql='select '''+@tablename+'''as 表名, count(*) 记录数 from '+@tablename exec(@sql) FETCH NEXT FROM cur INTO @tablename end close cur DEALLOCATE cur
用自定义函数实现得到表相应的结构(这段是抄来的) CREATE FUNCTION tablestructure (@objname nvarchar(111)) RETURNS @table table(fieldname varchar(50), type varchar(16), length int, nullable varchar(3)) BEGIN declare @objid int select @objid=id from sysobjects where id=object_id(@objname) insert @table select 'fieldname'=name, 'type' =type_name(xusertype), 'length'=convert(Int,length), 'nullable'=case when isnullable=0 then 'no' else 'yes' end from syscolumns where id=@objid and number=0 order by colid return END然后可用语句 select count(name) from tablestructure(你要查的表名)
select name as 表名,0 as 记录数 into #temp from sysobjects where type='u' declare @tablename varchar(100) declare @sql varchar(1000) declare cur cursor for select name from sysobjects where type='u' open cur FETCH NEXT FROM cur INTO @tablename while @@FETCH_STATUS = 0 begin set @sql='update #temp set 记录数= (select count(*) from '+@tablename+') where 表名='''+@tablename+'''' exec(@sql) FETCH NEXT FROM cur INTO @tablename end close cur DEALLOCATE curselect * from #temp
declare @sql varchar(1000)
declare cur cursor for select name from sysobjects where type='u'
open cur
FETCH NEXT FROM cur
INTO @tablename
while @@FETCH_STATUS = 0
begin
set @sql='select '''+@tablename+'''as 表名, count(*) 记录数 from '+@tablename
exec(@sql)
FETCH NEXT FROM cur
INTO @tablename
end
close cur
DEALLOCATE cur
CREATE FUNCTION tablestructure (@objname nvarchar(111))
RETURNS @table table(fieldname varchar(50),
type varchar(16),
length int,
nullable varchar(3))
BEGIN
declare @objid int
select @objid=id from sysobjects where id=object_id(@objname)
insert @table select
'fieldname'=name,
'type' =type_name(xusertype),
'length'=convert(Int,length),
'nullable'=case when isnullable=0 then 'no' else 'yes' end
from syscolumns where id=@objid and number=0 order by colid
return
END然后可用语句 select count(name)
from tablestructure(你要查的表名)
declare @tablename varchar(100)
declare @sql varchar(1000)
declare cur cursor for select name from sysobjects where type='u'
open cur
FETCH NEXT FROM cur
INTO @tablename
while @@FETCH_STATUS = 0
begin
set @sql='update #temp set 记录数= (select count(*) from '+@tablename+') where 表名='''+@tablename+''''
exec(@sql)
FETCH NEXT FROM cur
INTO @tablename
end
close cur
DEALLOCATE curselect * from #temp