/***************************************************************************************************|
| Procedure:          [SP_CRIAR_LISTA_TABELAS]                                                      |
| Data de Criação:    2024-02-23                                                                    |
| Autor:              Raphael Coelho                                                                |
|                                                                                                   |
| Objetivo:           Obtém uma lista de todas as tabelas físicas presentes no banco de dados       |
|                     atual, disponibilizando essas informações em uma tabela temporária global.    |
|                     Utiliza a stored procedure 'sp_msforeachdb', que itera por todos os bancos    |
|                     disponíveis no servidor conectado, incluindo o nome do servidor, banco,       |
|                     esquema e tabela.                                                             |
|                                                                                                   |
| Utilização:         EXEC [SP_CRIAR_LISTA_TABELAS]                                                 |
|                     SELECT * FROM ##LISTA_TABELAS                                                 |
***************************************************************************************************/

CREATE PROCEDURE [SP_CRIAR_LISTA_TABELAS]
AS
BEGIN
    /* ETAPA 1: RETORNA TODAS AS TABELAS DO BANCO */
    -- Desativa a contagem de linhas afetadas no painel de resultados
    SET NOCOUNT ON;

    -- Declara uma tabela de variáveis para armazenar o caminho das tabelas
    DECLARE @LISTA_TABELAS TABLE (CAMINHO_COMPLETO NVARCHAR(4000) COLLATE DATABASE_DEFAULT);
    
    -- Insere os caminhos das tabelas na tabela de variáveis usando a stored procedure 'sp_msforeachdb'
    INSERT INTO @LISTA_TABELAS (CAMINHO_COMPLETO)
    EXEC sp_msforeachdb 'SELECT @@SERVERNAME + ''.'' + ''?'' + ''.'' + B.name + ''.'' + A.name COLLATE DATABASE_DEFAULT
                           FROM [?].sys.tables AS A
                          INNER JOIN sys.schemas AS B
                             ON A.schema_id = B.schema_id';
        
    /* ETAPA 2: GERA A TABELA FINAL */
    -- Verifica se a tabela temporária já existe e, se sim, a remove
    IF OBJECT_ID('tempdb..##LISTA_TABELAS') <> 0
        DROP TABLE ##LISTA_TABELAS;

    -- Cria a tabela final com os dados das tabelas encontradas e as ordena por banco
    SELECT SUBSTRING(CAMINHO_COMPLETO, 1, CHARINDEX('.', CAMINHO_COMPLETO) - 1) AS NOME_SERVIDOR,
           SUBSTRING(CAMINHO_COMPLETO, CHARINDEX('.', CAMINHO_COMPLETO) + 1, 
                     CHARINDEX('.', CAMINHO_COMPLETO, CHARINDEX('.', CAMINHO_COMPLETO) + 1) - CHARINDEX('.', CAMINHO_COMPLETO) - 1) AS NOME_BANCO,
           SUBSTRING(CAMINHO_COMPLETO, CHARINDEX('.', CAMINHO_COMPLETO, CHARINDEX('.', CAMINHO_COMPLETO) + 1) + 1, 
                     CHARINDEX('.', CAMINHO_COMPLETO, CHARINDEX('.', CAMINHO_COMPLETO, CHARINDEX('.', CAMINHO_COMPLETO) + 1) + 1) - 
                     CHARINDEX('.', CAMINHO_COMPLETO, CHARINDEX('.', CAMINHO_COMPLETO) + 1) - 1) AS NOME_SCHEMA,
           SUBSTRING(CAMINHO_COMPLETO, CHARINDEX('.', CAMINHO_COMPLETO, CHARINDEX('.', CAMINHO_COMPLETO, CHARINDEX('.', CAMINHO_COMPLETO) + 1) + 1) + 1, 
                     LEN(CAMINHO_COMPLETO)) AS NOME_TABELA
      INTO ##LISTA_TABELAS
      FROM @LISTA_TABELAS
     WHERE CAMINHO_COMPLETO NOT LIKE '%tempdb%'
     ORDER BY 1, 2, 3, 4;

    -- Ativa novamente a contagem de linhas afetadas no painel de resultados
    SET NOCOUNT OFF;

    -- Mensagem de retorno
    PRINT('');
    PRINT('EXECUTE A CONSULTA ABAIXO PARA UTILIZAR A TABELA CRIADA.');
    PRINT('');
    PRINT('SELECT * FROM ##LISTA_TABELAS');
    PRINT('_______________________________________________________');
END;
GO