时间戳

首页 > 解决方案 > 使用 while 循环在 SQL Server 实例上创建数据库表和表信息

sql-server - 使用 while 循环在 SQL Server 实例上创建数据库表和表信息

问题描述

我想知道为什么我不能在以下 while 循环中用 DB 名称替换变量 @DBNAME：

我可以将变量用作 DB_NAME，但它不会让我用数据库名称替换 sys.tables

它给了我以下错误：“。”附近的语法不正确。

DROP TABLE #TABLE_INFO;
CREATE TABLE #TABLE_INFO
    (
    DB_NAME VARCHAR(100),
    TABLE_NAME VARCHAR(100),
    CREATE_DATE DATE
    );

DECLARE @COUNT AS INT = 1
DECLARE @DBNAME AS VARCHAR(100)

WHILE @COUNT < (SELECT MAX(DATABASE_ID) +1 FROM SYS.DATABASES)
BEGIN
    SET @DBNAME = (SELECT NAME FROM SYS.DATABASES WHERE DATABASE_ID = @COUNT)

    INSERT INTO #TABLE_INFO
    SELECT
        @DBNAME AS DB_NAME,
        A1.NAME AS TABLE_NAME,
        A1.CREATE_DATE
    FROM @DBNAME.SYS.TABLES A1

    SET @COUNT = @COUNT+1
END

SELECT * FROM #TABLE_INFO;

标签： sql-servervariableswhile-loop

解决方案

  • T-SQL 不允许对数据库对象标识符进行参数化。
    • ...因此您必须使用动态 SQL（即在字符串值中构建查询并将其传递给sp_executesql）。
    • 显然，由于存在 SQL 注入的风险，或者只是忘记正确转义名称（使用QUOTENAME），这可能会导致意外的数据丢失（例如，如果有人实际将表命名为[DELETE FROM Users]），这是很危险的。
    • 因此，尽可能少地使用动态 SQL 很重要。
    • 您可以做的是使用INSERT INTO @tableVariable EXECUTE sp_executesql @query它允许您将存储过程的结果 - 或任何动态 SQL - 存储到表变量或临时表中，而不会将它们直接输出到您的客户端连接，然后使用非动态处理结果SQL。
    • CROSS APPLY如果您可以将所有动态 SQL 逻辑移动到存储过程，您也可以使用。

尝试这个：

  • 顺便说一句，我将您的WHILE循环更改为使用 a来避免您对'工作STATIC READ_ONNLY CURSOR 方式的假设（例如，如果仅列出这 4 个值怎么办？您的循环将浪费时间检查）。sys.databasesdatabase_idsys.databasesdatabase_id1, 2, 99997, 99998WHILE3, 4, 5, 6, ..., 99996
  • 我还使用表变量而不是临时表，因为与临时表相比，表变量的范围和生命周期更容易推理。也就是说，与 TT 相比，TV 并没有真正的性能优势（尽管您可以将 TV 作为表值参数传递，这很好，而且通常比使用 TT 将数据传递给存储过程要好得多）。
DECLARE @results TABLE (
    DatabaseName nvarchar(100) NOT NULL,
    SchemaName   nvarchar(50)  NOT NULL,
    TableName    nvarchar(100) NOT NULL,
    Created      datetime      NOT NULL
);

DECLARE @dbName nvarchar(100);

DECLARE c CURSOR STATIC READ_ONLY FOR
    SELECT QUOTENAME( [name] ) FROM sys.databases;

OPEN c;

FETCH NEXT FROM c INTO @dbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    
    DECLARE @dbQuery nvarchar(1000) = N'

SELECT
    ''' + @dbName + N''' AS DatabaseName,
    s.[name]      AS SchemaName,
    t.[name]      AS TableName,
    t.create_date AS Created
FROM
    ' + @dbName + N'.sys.tables AS t
    INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
ORDER BY
    s.[name],
    t.[name]
';

    INSERT INTO @results
        ( DatabaseName, SchemaName, TableName, Created )
    EXECUTE sp_executesql @dbQuery;
    
    
    FETCH NEXT FROM c INTO @dbName;
    
END;
CLOSE c;
DEALLOCATE c;

--------------------------

SELECT
    *
FROM
    @results
ORDER BY
    DatabaseName,
    SchemaName,
    TableName;

我可以将此查询复制+粘贴到 SSMS 中，并在我的 SQL Server 2017 框中运行它而无需修改，它会返回预期的结果。

推荐阅读