Início / dba / Perguntas / 224118
Accepted
Tom_W
Asked: 2018-12-05 09:04:51 +0800 CST

SQL dinâmico - Como você pode usá-lo para executar um SELECT em todos os esquemas e tabelas?

  • 772

Cenário

Eu tenho um banco de dados que contém todos os dados de nossos clientes, que são separados por Esquemas. Por exemplo:

Customer1 = Schema1, 
Customer2 = Schema2, 
Customer3 = Schema3, 
etc...

Às vezes, temos um problema em que um valor de data em uma tabela às vezes é NULL. Esta tabela está contida em cada Schema, mas às vezes pode ser chamada de 'Tasks', 'Tasks_NEW' ou 'Task_NEW' (tem que amar consistência).

Estou procurando uma maneira de executar dinamicamente uma instrução SELECT que retornará uma contagem de todos os valores de data NULL, em todas as tabelas e todos os esquemas.

Até agora, fiz isso para colocar o Schema, mais a Tabela em uma TempTable;

CREATE TABLE #TASK_TABLES
(   ID      INT IDENTITY(1,1),
    TenantID        INT,
    TableName       NVARCHAR(255)   )

INSERT INTO #TASK_TABLES
SELECT TABLE_SCHEMA, TABLE_NAME
FROM [Dynamic].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME IN ('Tasks', 'Task_NEW', 'Tasks_NEW')
ORDER BY TABLE_SCHEMA

Agora que tenho essas informações, quero percorrê-las com um SELECT como este;

SELECT Schema, COUNT(Date)
FROM [Dynamic].[Schema].[Table] (NOLOCK)
WHERE DATE IS NULL

Infelizmente, não sei como escrever SQL dinâmico, pois ainda estou aprendendo, mas esperava que alguém pudesse me indicar a direção certa?

sql-server t-sql

2 respostas

  1. Best Answer

    Abaixo segue o código autoexplicativo. Alternativamente, você pode usar sp_ineachdb (escrito por Aaron Bertrand) .

     set nocount on
  -- create temp table .. 
 create table #commandsToRun (currentDB sysname, schemaName sysname, TableName sysname, columnName sysname, datatype sysname, commandToRun nvarchar(max))
 insert into #commandsToRun
SELECT   
        db_name() as CurrentDB, SCHEMA_NAME(tbl.schema_id) as SchemaName, tbl.name AS TableName,
         c.name AS ColumnName, tp.name as DataType, 
         N'select count('+c.name+') as TotalDateColumnAsNULL, '
                    + ''''+SCHEMA_NAME(tbl.schema_id)+'''' + ' as  SchemaName, '
                    + ''''+tbl.name + ''''+ ' AS TableName' + ''
                    +' from '+ db_name() +'.' + SCHEMA_NAME(tbl.schema_id) 
                    +'.'+ tbl.name +' with (nolock) where '
                    +c.name+' is NULL'as CommandToRun
FROM
  sys.columns c
JOIN sys.tables tbl ON
  c.object_id = tbl.object_id
JOIN sys.types tp on c.user_type_id = tp.user_type_id
WHERE
  ( c.name = 'Date' -- filter the column that you want to check 
  or tp.name = 'datetime' -- filter the datatype if you dont know the column names 
  )
-- check the contents of the table 
select * from #commandsToRun

-- generate exec() to run the sql generated in the CommandToRun

declare @sqltext nvarchar(max) = N''

select  @sqltext += N'exec ('''+replace(CommandToRun,'''','''''')+'''); '
from #commandsToRun

-- uncomment below to run the sql generated.
-- exec(@sqltext)
    • 1

  2. Eu tenho uma abordagem um pouco diferente para esse tipo de seleção dinâmica.

    Primeiro, crie todas as instruções SELECT que você deseja fazer e coloque-as em uma variável de tabela temporária / tabela temporária

    CREATE TABLE #SQL_COMMANDS (
    ID      INT IDENTITY(1,1),
    SQLCmd  NVARCHAR(500)   
)

INSERT INTO #SQL_COMMANDS (SQLcmd)
SELECT 'SELECT schema, Count(*)
FROM [Dynamic].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WITH (NOLOCK) WHERE DATE IS NULL'
FROM [Dynamic].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME IN ('Tasks', 'Task_NEW', 'Tasks_NEW')
ORDER BY TABLE_SCHEMA

    Em seguida, concatene todos os comandos em uma string, colocando UNION ou UNION ALL entre cada comando, assim:

    DECLARE @FullSQL NVARCHAR(MAX)

SELECT @FullSQL = IsNull(@FullSQL + '
UNION ALL
', '' ) + SQLCmd 
FROM #SQL_COMMANDS

    Por fim, execute o comando:

    EXEC (@FullSQL)

    Isso remove cursores e outras construções de loop de seu SQL.

    • 0

relate perguntas