查询特定数据库里所有表里面的记录总数

来源：互联网发布：mac cad2014 打印pdf 编辑：程序博客网时间：2024/05/21 07:52

DECLARE @TableInfo TABLE
(
          name VARCHAR(50) ,
          [rows] int ,
          reserved VARCHAR(50) ,
          data VARCHAR(50) ,
          index_size VARCHAR(50) ,
          unused VARCHAR(50)
        )
    DECLARE @TableName TABLE ( name VARCHAR(50) )
    DECLARE @name VARCHAR(50)
    INSERT INTO @TableName
            ( name
            )
            SELECT o.name
            FROM    sysobjects o ,
                    sysindexes i
            WHERE   o.id = i.id
                    AND o.Xtype = 'U'
                    AND i.indid < 2
            ORDER BY i.rows DESC ,
                    o.name

    WHILE EXISTS ( SELECT   1
                   FROM     @TableName )
        BEGIN
            SELECT TOP 1
                    @name = name
            FROM    @TableName
            DELETE @TableName WHERE name=@name
            INSERT INTO @TableInfo
                    ( name ,
                      [rows] ,
                      reserved ,
                      data ,
                      index_size ,
                      unused

                    )
                    EXEC sys.sp_spaceused @name
        END
            SELECT sum(rows) FROM @TableInfo

0 0