统计行数_sql server 2012中统计表行数的四种方法

--01.使用sys.partitions和sys.tables
SELECT OBJECT_NAME(object_id) AS object_name,
       rows
FROM sys.partitions
WHERE index_id IN ( 0, 1 )
      AND object_id IN (
                           SELECT object_id FROM sys.tables
                       );

GO
--02.使用sp_spaceused
--step01，构建表函数
DECLARE @table_rows TABLE
(
    name NVARCHAR(MAX),
    rows BIGINT,
    reserved NVARCHAR(100),
    data NVARCHAR(100),
    index_size NVARCHAR(100),
    unused NVARCHAR(100)
);
INSERT INTO @table_rows
(
    name,
    rows,
    reserved,
    data,
    index_size,
    unused
)
EXEC sp_MSforeachtable @command1 = 'exec sp_spaceused ''?''';

SELECT a.name,
       a.rows,
       a.reserved,
       a.data,
       a.index_size,
       a.unused
FROM @table_rows AS a;
--03，使用sysobject和sysindexes
SELECT a.name,
       b.rows
FROM sysobjects a
    INNER JOIN sysindexes AS b
        ON a.id = b.id
WHERE a.xtype = 'u'
      AND b.indid IN ( 0, 1 );
--4.动态脚本count方法
DECLARE @icount INT;
DECLARE @ctname NCHAR(50);
DECLARE @cschema NVARCHAR(10);
DECLARE @csql NVARCHAR(MAX) = '';
DECLARE c1 CURSOR FOR
SELECT TABLE_SCHEMA,
       TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE';
OPEN c1;
FETCH NEXT FROM c1
INTO @cschema,
     @ctname;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @csql
        = 'select @icount=count(*) from ' + @cschema + '.' + @ctname
          + ' print @cschema+''.''+@ctname+cast(@icount as char(10))';
    EXECUTE sp_executesql @csql,
                          N'@icount int out,@cschema nvarchar(10) out,@ctname nchar(50) out',
                          @icount OUT,
                          @cschema OUT,
                          @ctname OUT;
    FETCH NEXT FROM c1
    INTO @cschema,
         @ctname;
END;
CLOSE c1;
DEALLOCATE c1;