Engenho & Engenhocas

Com obter o nª de registos de cada tabela para todas as bases de dados de um servidor

Para ler

SET NOCOUNT ON;

CREATE TABLE #Databases
(DatabaseID   INT, 
 DatabaseName SYSNAME
);

CREATE TABLE #TableRows
(TableCatalog  SYSNAME, 
 TableSchema   SYSNAME, 
 TableName     SYSNAME,
 NrRows     Int
);

INSERT INTO #Databases
(DatabaseID, 
 DatabaseName
)
       SELECT database_id, 
              [name]
       FROM sys.databases
       WHERE [state] <> 6 /* ignore offline DBs */
             AND database_id > 4 /* ignore system DBs */


DECLARE @databaseID INT, @databaseName SYSNAME, @sql VARCHAR(MAX);

WHILE
(
    SELECT COUNT(*)
    FROM #Databases
) > 0
    BEGIN
        SELECT TOP 1 @databaseID = databaseID, 
                     @databaseName = databaseName
        FROM #Databases;

        SET @sql = 'INSERT INTO #TableRows SELECT DB_NAME(' + CONVERT(VARCHAR, @databaseID) + '), ISNULL(OBJECT_SCHEMA_NAME(TBL.object_id), ''dbo'') ''Schema'', TBL.name ''Table'', SUM(PART.rows) ''NrRows''
FROM ' + QUOTENAME(@databaseName) + '.sys.tables TBL
INNER JOIN ' + QUOTENAME(@databaseName) + '.sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN ' + QUOTENAME(@databaseName) + '.sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name';

        EXEC (@sql);

        DELETE FROM #Databases
        WHERE databaseID = @databaseID;

    END;

SET NOCOUNT OFF;

SELECT *
FROM #TableRows A
ORDER BY A.TableCatalog, 
         A.TableSchema, 
         A.TableName;

DROP TABLE #TablesRows;
DROP TABLE #Databases;

Referência: https://www.brentozar.com/archive/2014/02/count-number-rows-table-sql-server/, https://stackoverflow.com/questions/13757387/getting-sql-server-cross-database-dependencies

Ler mais Comentar