--参考一下:查询每个表的记录数exec sp_msforeachtable 'select ''?'' as ''表名'',(select count(1) from ?) as ''行数'''
declare @tablename varchar(100) declare @sql nvarchar(4000) declare @Num int declare tb_cursor scroll cursor for select name from sysobjects where xtype='U' open tb_cursor fetch next from tb_cursor into @tablename while @@fetch_status=0 begin set @sql=N'select @Num=count(1) from '+@TableName exec sp_executesql @sql,N'@Num int out',@Num out if @Num>0 print @tablename fetch next from tb_cursor into @tablename end close tb_cursor deallocate tb_cursor
--查记录数不为空的所有表 select a.name from sysobjects a , syscolumns b , sysindexes c where a.id=b.id and a.name=c.name and a.type='u' and c.rows<>0
select o.name,i.rows from sysobjects o inner join sysindexes i on o.id=i.id where xtype='u' and OBJECTPROPERTY(o.id, N'IsUserTable') = 1 and i.rows>0当然,sysindexes表中的rows的更新不一定是很及时的, 你只是为了查看一下表中有无数据,所以这个还是可以利用到的.
如果用楼上的,最好加上关键字DISTINCT select DISTINCT a.name from sysobjects a , syscolumns b , sysindexes c where a.id=b.id and a.name=c.name and a.type='u' and c.rows<>0
select DISTINCT a.name,c.rows--查看行数 from sysobjects a , syscolumns b , sysindexes c where a.id=b.id and a.name=c.name and a.type='u' and c.rows<>0
declare @tablename varchar(100) declare @sql nvarchar(4000) declare @Num int declare @i int declare tb_cursor scroll cursor for select name from sysobjects where xtype='U' open tb_cursor fetch next from tb_cursor into @tablename while @@fetch_status=0 begin set @sql=N'select @Num=count(1) from '+@TableName exec sp_executesql @sql,N'@Num int out',@Num out if @Num>0 begin print @tablename set @i=isnull(@i,0)+1 end fetch next from tb_cursor into @tablename end close tb_cursor deallocate tb_cursor print @i------ select DISTINCT o.name from sysobjects o inner join sysindexes i on o.id=i.id where xtype='u' and OBJECTPROPERTY(o.id, N'IsUserTable') = 1 and i.rows>0 select DISTINCT a.name,c.rows from sysobjects a , syscolumns b , sysindexes c where a.id=b.id and a.name=c.name and a.type='u' and c.rows<>0------------------ 发现,前面两个返回的结果一致,而最后一个与前两个不一致,谁解释一下
select * from sysobjects where type='U'
select sys.objects.name from sys.objects join sys.sysindexes on sys.objects.[object_id]=sys.sysindexes.[id] where sys.objects.type='U' and sys.sysindexes.rowcnt>0 group by sys.objects.name
CREATE TABLE dbo.#(tab_name sysname)sp_msforeachtable 'IF (SELECT COUNT(*) FROM (SELECT TOP 1 * FROM ?) a)>0 INSERT INTO # VALUES(''?'')'SELECT COUNT(*) FROM # ----------- 238(1 row(s) affected)
发现,前面两个返回的结果一致,而最后一个与前两个不一致,谁解释一下 ----------------------------------------------------------------- select DISTINCT a.name,c.rows from sysobjects a , syscolumns b , sysindexes c where a.id=b.id and a.name=c.name and a.type='u' and c.rows<>0 这个不对,不能用a.name=c.name 作连接条件 sysindexes.name是索引名应该这样,用id作连接条件 select distinct a.name,c.rows from sysobjects a , sysindexes c where a.id=c.id and a.type='u' and c.rows<>0
declare @sql nvarchar(4000)
declare @Num int
declare tb_cursor scroll cursor for select name from sysobjects where xtype='U'
open tb_cursor
fetch next from tb_cursor into @tablename
while @@fetch_status=0
begin
set @sql=N'select @Num=count(1) from '+@TableName
exec sp_executesql @sql,N'@Num int out',@Num out
if @Num>0 print @tablename
fetch next from tb_cursor into @tablename
end
close tb_cursor
deallocate tb_cursor
select a.name
from sysobjects a , syscolumns b , sysindexes c
where a.id=b.id and a.name=c.name
and a.type='u' and c.rows<>0
inner join sysindexes i
on o.id=i.id
where xtype='u' and OBJECTPROPERTY(o.id, N'IsUserTable') = 1 and i.rows>0当然,sysindexes表中的rows的更新不一定是很及时的, 你只是为了查看一下表中有无数据,所以这个还是可以利用到的.
select DISTINCT a.name
from sysobjects a , syscolumns b , sysindexes c
where a.id=b.id and a.name=c.name
and a.type='u' and c.rows<>0
from sysobjects a , syscolumns b , sysindexes c
where a.id=b.id and a.name=c.name
and a.type='u' and c.rows<>0
declare @sql nvarchar(4000)
declare @Num int
declare @i int
declare tb_cursor scroll cursor for select name from sysobjects where xtype='U'
open tb_cursor
fetch next from tb_cursor into @tablename
while @@fetch_status=0
begin
set @sql=N'select @Num=count(1) from '+@TableName
exec sp_executesql @sql,N'@Num int out',@Num out
if @Num>0
begin
print @tablename
set @i=isnull(@i,0)+1
end
fetch next from tb_cursor into @tablename
end
close tb_cursor
deallocate tb_cursor
print @i------
select DISTINCT o.name from sysobjects o
inner join sysindexes i
on o.id=i.id
where xtype='u' and OBJECTPROPERTY(o.id, N'IsUserTable') = 1 and i.rows>0 select DISTINCT a.name,c.rows
from sysobjects a , syscolumns b , sysindexes c
where a.id=b.id and a.name=c.name
and a.type='u' and c.rows<>0------------------
发现,前面两个返回的结果一致,而最后一个与前两个不一致,谁解释一下
sys.objects join sys.sysindexes
on sys.objects.[object_id]=sys.sysindexes.[id]
where sys.objects.type='U' and sys.sysindexes.rowcnt>0
group by sys.objects.name
-----------
238(1 row(s) affected)
-----------------------------------------------------------------
select DISTINCT a.name,c.rows
from sysobjects a , syscolumns b , sysindexes c
where a.id=b.id and a.name=c.name
and a.type='u' and c.rows<>0
这个不对,不能用a.name=c.name 作连接条件
sysindexes.name是索引名应该这样,用id作连接条件
select distinct a.name,c.rows
from sysobjects a , sysindexes c
where a.id=c.id and a.type='u' and c.rows<>0