Home > Article > Database > SQL Server 查询数据库中所有表数据条数

SQL Server 查询数据库中所有表数据条数

WBOYOriginal: 2016-06-07 17:49:284103browse

有的时间我想把数据库中表的记录统计一下，如果我们一个一个表的操作可以直接select count(*) from tablename就可以然后一个个相加，但是如果有上百个表有没有更简单的方法呢,下面我总结了一些方法有需要的朋友可参考。

如果是要得到中所有表的条数呢？我们来看几种最常见的方式：

--方法一

代码如下

复制代码

b.name as tablename ,

c.row_count as datacount

from sys.indexes a ,

sys.objects b ,

sys.dm_db_partition_stats c

where a.[object_id] = b.[object_id]

AND b.[object_id] = c.[object_id]

AND a.index_id = c.index_id

AND a.index_id

AND b.is_ms_shipped = 0

--方法二

代码如下

复制代码

select b.name as tablename ,

a.rowcnt as datacount

from sysindexes a ,

sysobjects b

where a.id = b.id

and a.indid

and objectproperty(b.id, 'IsMSShipped') = 0

--方法三

代码如下

复制代码

if exists ( select *
            from    dbo.sysobjects
            where   id = object_id(N'[dbo].[TableSpace]')
                    and objectproperty(id, N'IsUserTable') = 1 )
    drop table [dbo].[TableSpace]
go
create table TableSpace
    (
      TableName varchar(20) ,
      RowsCount char(11) ,
      Reserved varchar(18) ,
      Data varchar(18) ,
      Index_size varchar(18) ,
      Unused varchar(18)
    )
go
declare @sql varchar(500)
declare @TableName varchar(20)
declare mCursor cursor
for
select name from sysobjects where xtype='U'
open mCursor
fetch NEXT from mCursor into @TableName
while @@fetch_status = 0
    begin
        set @sql = 'insert into TableSpace '
        set @sql = @sql + ' exec sp_spaceused ''' + @TableName + ''' '
        exec (@sql)
        fetch NEXT from mCursor into @TableName
    end
close mCursor
deallocate mCursor
go
--显示结果
select TableName,RowsCount from TableSpace

--建议使用后两种方式，对于SQL SERVER 2005来说，三种方法都好使，如果是其他板本，可以逐一测试一下。

方法四

--==========================================================================
-- 说明：本脚本用于查询当前中所有表格的记录条数
-- 并将结果存入tableinfo表中，不会删除以备用户再做处理与分析
-- 不过，最后请用户删除此表。
--==========================================================================

代码如下

复制代码

if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[tablespace]) and objectproperty(id, nisusertable) = 1)
drop table [dbo].[tablespace]
go

create table tablespace
(
tablename varchar(20),
rowscount char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
go

declare @sql varchar(500)
declare @tablename varchar(20)

declare cursor1 cursor
for
select name from sysobjects where xtype=u

open cursor1
fetch next from cursor1 into @tablename

while @@fetch_status = 0
begin
set @sql = insert into tablespace
set @sql = @sql + exec sp_spaceused + @tablename +
exec (@sql)
fetch next from cursor1 into @tablename
end
close cursor1
deallocate cursor1
go

--显示结果
select * from tablespace
--order by tablename
--order by tablename asc --按表名称，用于统计表
--order by rowscount desc --按行数量，用于查看表行数
--order by reserved desc, data desc --按占用空间
--order by index_size desc, reserved desc --按索引空间查看
go

--查看库的使用状况,可以随时执行的。
--exec sp_spaceused
--go

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：SQLServer数据库表架构和数据保存成sql文件Next article：sql数据分组（GROUP BY）最大值，第一条，前三条实例

See more

SQL Server 查询数据库中所有表数据条数

Related articles