Boa tarde Nando...
Eu tenho esse script que pode te ajudar...
CREATE TABLE #TEMP(
REC_ID INT IDENTITY (1, 1),
TABLE_NAME VARCHAR(128) NULL,
NBR_OF_ROWS INT NULL,
DATA_SPACE DECIMAL(15,2) NULL,
INDEX_SPACE DECIMAL(15,2) NULL,
TOTAL_SIZE DECIMAL(15,5) NULL,
PERCENT_OF_DB DECIMAL(15,5) NULL,
DB_SIZE DECIMAL(15,5) NULL
)
-- OBTÉM TODAS AS TABELAS, NOMES E RESPECTIVOS TAMANHOS
EXEC SP_MSFOREACHTABLE @COMMAND1="INSERT INTO #TEMP(NBR_OF_ROWS, DATA_SPACE, INDEX_SPACE) EXEC SP_MSTABLESPACE '?'",
@COMMAND2="UPDATE #TEMP SET TABLE_NAME = '?' WHERE REC_ID = (SELECT MAX(REC_ID) FROM #TEMP)"
-- DEFINE O TAMANHO TOTAL E O TAMANHO TOTAL NO DATABASE
UPDATE #TEMP
SET TOTAL_SIZE = (DATA_SPACE + INDEX_SPACE)/1048576, DB_SIZE = (SELECT SUM(DATA_SPACE + INDEX_SPACE) FROM #TEMP)/1048576
-- DEFINE O PERCENTUAL OCUPADO PELA TABELA DENTRO DA BASE
UPDATE #TEMP
SET PERCENT_OF_DB = (TOTAL_SIZE/DB_SIZE) * 100
-- LISTA AS TABELAS
SELECT
REPLACE(REPLACE(UPPER(TABLE_NAME),'[',''),']','') AS 'NOME DA TABELA',
NBR_OF_ROWS AS 'NÚMERO DE LINHAS',
REPLACE(CONVERT(VARCHAR(255),(DATA_SPACE/1048576)),'.',',')+' GB' AS 'TAMANHO DOS DADOS',
REPLACE(CONVERT(VARCHAR(255),(INDEX_SPACE/1048576)),'.',',')+' GB' AS 'TAMANHO DOS ÍNDICES',
REPLACE(CONVERT(VARCHAR(255),(TOTAL_SIZE)),'.',',')+' GB' AS 'TAMANHO TOTAL',
REPLACE(CONVERT(VARCHAR(255),(PERCENT_OF_DB)),'.',',')+' %' AS 'PORCENTAGEM',
REPLACE(CONVERT(VARCHAR(255),(DB_SIZE)),'.',',')+' GB' AS 'TAMANHO TOTAL DA BASE'
FROM #TEMP
ORDER BY 2
-- EXCLUI A TABELA TEMPORÁRIA
DROP TABLE #TEMP
Att.
Rafael Melo
