SQL server 统计库中记录数

 

统计单个库所有表记录数(包含sys)

SELECT  SCHEMA_NAME(t.schema_id) AS [schema] ,t.name AS tableName ,i.rows AS [rowCount]
FROM    sys.tables AS t ,
        sysindexes AS i
WHERE   t.object_id = i.id  AND i.indid <= 1 AND i.rows>0 order by t.name asc

 

 

 

统计单个库的所有表,包含总数(包含sys)

set nocount on
if object_id(N'tempdb.db.#temp') is not null
  drop table #temp
create table #temp (name sysname,count numeric(18))

insert into #temp
select o.name,i.rows
from sysobjects o,sysindexes i  
where o.id=i.id and o.Xtype='U' and i.indid<2

select count(count) 总表数,sum(count) 总记录数 from #temp
select * from #temp order by count desc
set nocount off

 

 

 

 

统计单个库所有表的详细信息,不包含sys

CREATE TABLE #tmptb(tbname sysname,tbrows int,tbREserved varchar(10),tbData varchar(10),tbIndexSize varchar(10),tbUnUsed varchar(10)) 
INSERT INTO #tmptb exec sp_MSForEachTable 'EXEC sp_spaceused ''?''' 
SELECT * from #tmptb --列出所有表的情况 
SELECT tbrows,tbname FROM #tmptb WHERE tbrows=0 --列出记录数据为0的表 
ORDER BY tbname 
DROP TABLE #tmptb