Web-automation.ru

Существует несколько вариантов, чтобы получить количество строк для всех таблиц в БД:

• sys.partitions Catalog View

  SELECT
        QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
        , SUM(sPTN.Rows) AS [RowCount]
  FROM 
        sys.objects AS sOBJ
        INNER JOIN sys.partitions AS sPTN
              ON sOBJ.object_id = sPTN.object_id
  WHERE
        sOBJ.type = 'U'
        AND sOBJ.is_ms_shipped = 0x0
        AND index_id < 2 -- 0:Heap, 1:Clustered
  GROUP BY 
        sOBJ.schema_id
        , sOBJ.name
  ORDER BY [TableName]
  GO

• sys.dm_db_partition_stats Dynamic Management View (DMV)

  SELECT
        QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
        , SUM(sdmvPTNS.row_count) AS [RowCount]
  FROM
        sys.objects AS sOBJ
        INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
              ON sOBJ.object_id = sdmvPTNS.object_id
  WHERE 
        sOBJ.type = 'U'
        AND sOBJ.is_ms_shipped = 0x0
        AND sdmvPTNS.index_id < 2
  GROUP BY
        sOBJ.schema_id
        , sOBJ.name
  ORDER BY [TableName]
  GO

• sp_MSforeachtable System Stored Procedure

  DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
  INSERT INTO @TableRowCounts ([TableName], [RowCount])
  EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
  SELECT [TableName], [RowCount]
  FROM @TableRowCounts
  ORDER BY [TableName]
  GO

• COALESCE() Function

  DECLARE @QueryString NVARCHAR(MAX) ;
  SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                        + 'SELECT '
                        + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                        + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                        , COUNT(*) AS [RowCount] FROM '
                        + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                        + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
  FROM sys.objects AS sOBJ
  WHERE
        sOBJ.type = 'U'
        AND sOBJ.is_ms_shipped = 0x0
  ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
  EXEC sp_executesql @QueryString
  GO

Ссылка на источник

Периодически мы проводим обучение и даем возможность стажировки на базе нашей платформы с управлением на SQL. Если вам это интересно, то пожалуйста посмотрите информацию об обучении/стажировке по SQL.