SELECT a.name 表名,b.rows 当前记录数 FROM dbo.sysobjects a INNER join dbo.sysindexes b ON a.id = b.id WHERE objectproperty(a.id,N'IsUserTable')=1
--用下面这个就可以了.--得到数据库中所有表的空间/记录情况create table #tb(表名 sysname,记录数 int ,保留空间 varchar(10),使用空间 varchar(10) ,索引使用空间 varchar(10),未用空间 varchar(10))insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''select * from #tbgo drop table #tb
create table stat(name varchar(30),coun int) go declare @a varchar(30) declare cur cursor for select name from sysobjects where xtype='U' open cur fetch next from cur into @awhile @@fetch_status=0 begin exec('insert into stat select '''+ @a+ ''' ,count(*) from '+@a) fetch next from cur into @a end close cur deallocate curselect * from stat
create table #tb(表名 sysname,记录数 int)insert into #tb exec sp_msforeachtable 'select ''?'',count(*) from ?'select * from #tbdrop table #tb
刚才那样有点错,应该这样:SELECT a.name 表名,b.rows 当前记录数 FROM dbo.sysobjects a INNER join dbo.sysindexes b ON a.name = b.name WHERE objectproperty(a.id,N'IsUserTable')=1
select o.name,rows,i.indid from sysobjects o left join sysindexes i on o.id=i.id where o.xtype='U' and (i.indid=0 or i.indid=1)
或者 SELECT a.name 表名,max(b.rows) 当前记录数 FROM dbo.sysobjects a INNER join dbo.sysindexes b ON a.id = b.id WHERE objectproperty(a.id,N'IsUserTable')=1 group by a.name
SELECT a.name 表名,b.rows 当前记录数 FROM dbo.sysobjects a INNER join dbo.sysindexes b ON a.id = b.id WHERE objectproperty(a.id,N'IsUserTable')=1 and b.indid<2 and a.status>0
FROM dbo.sysobjects a
INNER join dbo.sysindexes b
ON a.id = b.id
WHERE objectproperty(a.id,N'IsUserTable')=1
,保留空间 varchar(10),使用空间 varchar(10)
,索引使用空间 varchar(10),未用空间 varchar(10))insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''select * from #tbgo
drop table #tb
go
declare @a varchar(30)
declare cur cursor for select name from sysobjects where xtype='U'
open cur
fetch next from cur
into @awhile @@fetch_status=0
begin
exec('insert into stat select '''+ @a+ ''' ,count(*) from '+@a)
fetch next from cur
into @a
end
close cur
deallocate curselect * from stat
FROM dbo.sysobjects a
INNER join dbo.sysindexes b
ON a.name = b.name
WHERE objectproperty(a.id,N'IsUserTable')=1
SELECT a.name 表名,max(b.rows) 当前记录数
FROM dbo.sysobjects a
INNER join dbo.sysindexes b
ON a.id = b.id
WHERE objectproperty(a.id,N'IsUserTable')=1 group by a.name
FROM dbo.sysobjects a
INNER join dbo.sysindexes b
ON a.id = b.id
WHERE objectproperty(a.id,N'IsUserTable')=1 and b.indid<2 and a.status>0