主页 / dba / 问题 / 224118
Accepted
Tom_W
Asked: 2018-12-05 09:04:51 +0800 CST

动态 SQL - 如何使用它对所有模式和表运行 SELECT？

  • 772

设想

我有一个包含我们所有客户数据的数据库，这些数据由模式分隔。例如：

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

有时我们会遇到表中的 Date 值有时为 NULL 的问题。该表包含在每个 Schema 中，但有时可以称为“Tasks”、“Tasks_NEW”或“Task_NEW”（喜欢一致性）。

我正在寻找一种可以动态运行 SELECT 语句的方法，该语句将返回所有表和所有模式的所有 NULL 日期值的计数。

到目前为止，我这样做是为了将 Schema，加上 Table 放入一个 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

现在我有了这些信息，我想用这样的 SELECT 循环遍历它；

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

可悲的是，我还不完全知道如何编写动态 SQL，因为我仍在学习，但我希望有人能够指出我正确的方向？

sql-server t-sql

2 个回答

  1. Best Answer

    下面是自我解释的代码。或者，您可以使用sp_ineachdb（由 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. 对于这种类型的动态选择，我有一个稍微不同的方法。

    首先，创建您要执行的所有 SELECT 语句，并将它们放入临时表/临时表变量中

    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

    然后，将所有命令连接成一个字符串，在每个命令之间放置 UNION 或 UNION ALL，如下所示：

    DECLARE @FullSQL NVARCHAR(MAX)

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

    最后，运行命令：

    EXEC (@FullSQL)

    这会从 SQL 中删除游标和其他循环结构。

    • 0

相关问题