number of rows and columns of an instance

IF OBJECT_ID('TEMPDB.DBO.#TEMPORARIA') IS NOT NULL
DROP TABLE #TEMPORARIA

CREATE TABLE #TEMPORARIA (
BASE VARCHAR(100),
NOME VARCHAR(200),
LINHAS VARCHAR(100),
RESERVED VARCHAR(100),
DADOS VARCHAR(100),
INDICE VARCHAR(100),
UNSED VARCHAR(100),
DATA_CRIACAO DATETIME,
ULTIMA_VERIFICACAO DATETIME,
USUARIO_CRIACAO VARCHAR(10),
QUANTIDADE_COLUNAS INT
)
DECLARE @CMD VARCHAR(1000),@i int ,@base varchar(100)

set @i = 1

IF OBJECT_ID('TempDB.dbo.#BASES') is not null
drop table #BASES

SELECT 
quotename(NAME) AS BASE,
ROW_NUMBER() OVER(ORDER BY NAME) AS CONTADOR
INTO #BASES
FROM SYS.DATABASES
WHERE NAME NOT IN ('MASTER','TEMPDB','MODEL','MSDB')

while @i <=(select max(contador) from #bases)
begin
SET @BASE = (SELECT BASE FROM #BASES WHERE CONTADOR = @i)
SET @CMD = 
'EXEC ' + @BASE + '.DBO.SP_MSFOREACHTABLE ''INSERT INTO #TEMPORARIA (NOME,LINHAS,RESERVED,DADOS,INDICE,UNSED) EXEC SP_SPACEUSED ''''?'''''''

EXEC(@CMD)

EXEC('UPDATE A SET A.BASE = '''+@BASE+'''
FROM #TEMPORARIA A WITH(NOLOCK)
WHERE BASE IS NULL;

UPDATE A SET A.DATA_CRIACAO = B.CREATE_DATE
FROM #TEMPORARIA A
JOIN '+@BASE+'.SYS.TABLES B
ON A.NOME = B.NAME COLLATE DATABASE_DEFAULT
WHERE A.BASE = '''+@BASE+''' 

UPDATE A SET A.QUANTIDADE_COLUNAS = B.Q
FROM #TEMPORARIA A
JOIN (SELECT TABLE_NAME,COUNT(*) Q
FROM '+@BASE+'.INFORMATION_SCHEMA.COLUMNS GROUP BY TABLE_NAME) B
ON A.NOME = B.TABLE_NAME COLLATE DATABASE_DEFAULT
WHERE A.BASE = '''+@BASE+''' ')

SET @I = @i + 1
end
GO
ALTER TABLE #TEMPORARIA ADD GB FLOAT, MB FLOAT
GO
UPDATE #TEMPORARIA SET GB = CAST(LTRIM(RTRIM(REPLACE(DADOS,'KB',''))) AS FLOAT) /1024. /1024.,
MB = CAST(LTRIM(RTRIM(REPLACE(DADOS,'KB',''))) AS FLOAT) /1024.

---ACESSO NA TABELA
if object_id('tempdb.dbo.#UsoTabelas') is not null
drop table tempdb.dbo.#UsoTabelas
go
create table tempdb.dbo.#UsoTabelas (banco varchar(100),Tabela varchar(255),ultima_pesquisa datetime,ultima_verificacao datetime,
ultima_busca datetime,ultima_atualizacao datetime)
go


SELECT 
BASE AS BASE,
QUOTENAME(NOME) AS NOME,
LINHAS AS NUM_LINHAS,
QUANTIDADE_COLUNAS AS COLUNAS,
CAST(GB AS DECIMAL(10,2) ) AS GB,
CAST(MB AS DECIMAL(10,2) ) AS MB
--'exec '+BASE + '.sys.sp_rename ''' + NOME + ''',''' + nome + '_DELETAR_20180419'''
--select sum(CAST(gb AS DECIMAL(10,2) )) --select distinct base
FROM #TEMPORARIA
ORDER BY 4