循环遍历数据库列表以删除并重新创建视图

发布于 2024-12-25 10:40:48 字数 1019 浏览 1 评论 0原文

我不确定是否可以使用 TSQL 中的循环来完成此操作。

我有一个数据库列表，我想循环遍历每个数据库，并根据需要重新创建视图。

我已经有一个脚本可以删除并重新创建视图。然而，目前在我的脚本顶部有一堆 use 语句，我只需沿着列表取消注释并重新运行脚本即可。不过我想将其自动化，这样会更快。我过去做过类似的事情，循环遍历数据库名称字符串并使用它来执行 use 语句。除了创建视图时以外，它都有效，因为视图必须由 go 语句分隔。这是我的代码的代码片段，用于解析数据库，任何有助于处理视图的帮助都会很棒。

DECLARE @DBs AS VARCHAR(MAX);
DECLARE @OneDB AS VARCHAR(255);
DECLARE @CmdToExec AS VARCHAR(MAX);

SET @DBs = 'db1,db2,db3,db4,db5,db6,db7,db8,db9,db10,db11,db12,db13,db14,db15,db16'

DECLARE DB_Cursor CURSOR FOR
    SELECT SUBSTRING( ',' + @DBs + ',', n + 1,
        CHARINDEX( ',', ',' + @DBs + ',', n + 1 ) - n - 1 ) AS "dbInfo"
    FROM CommunityPAL.dbo.Numbers
    WHERE SUBSTRING( ',' + @DBs + ',', n, 1 ) = ','
        AND n < LEN( ',' + @DBs + ',' );
OPEN DB_Cursor;
FETCH NEXT FROM DB_Cursor INTO @OneDB;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CmdToExec = 'USE ' + @OneDB;
    EXEC @CmdToExec;

    --statements to execute

    FETCH NEXT FROM DB_Cursor INTO @OneDB;
END
CLOSE DB_Cursor
DEALLOCATE DB_Cursor

原文

I am unsure if it is possible to do this with a loop in TSQL.

I have a list of Databases I would like to loop through and for each database drop and recreate the view as necessary.

I have a script already to drop and recreate the view. However, presently at the top of my script I have a bunch of use statements and i just go down the list uncommenting and reruning the script. However I would like to automate it so it would be much faster. I have done a similar thing in the past looping though a string of database names and using it to execute a use statement. Which works except when creating views because the view has to be seperated by go statements. Here is a code snippet of my code to parse databases any help to make work with views would be great.

DECLARE @DBs AS VARCHAR(MAX);
DECLARE @OneDB AS VARCHAR(255);
DECLARE @CmdToExec AS VARCHAR(MAX);

SET @DBs = 'db1,db2,db3,db4,db5,db6,db7,db8,db9,db10,db11,db12,db13,db14,db15,db16'

DECLARE DB_Cursor CURSOR FOR
    SELECT SUBSTRING( ',' + @DBs + ',', n + 1,
        CHARINDEX( ',', ',' + @DBs + ',', n + 1 ) - n - 1 ) AS "dbInfo"
    FROM CommunityPAL.dbo.Numbers
    WHERE SUBSTRING( ',' + @DBs + ',', n, 1 ) = ','
        AND n < LEN( ',' + @DBs + ',' );
OPEN DB_Cursor;
FETCH NEXT FROM DB_Cursor INTO @OneDB;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CmdToExec = 'USE ' + @OneDB;
    EXEC @CmdToExec;

    --statements to execute

    FETCH NEXT FROM DB_Cursor INTO @OneDB;
END
CLOSE DB_Cursor
DEALLOCATE DB_Cursor

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

长发绾君心 2025-01-01 10:40:49

我很确定你不能只在 T-SQL 中做你想做的事。 CREATE VIEW（和ALTER VIEW）不使用三部分命名约定 (database.schema.object)。它必须是批处理中的唯一语句，因此不能与动态 SQL 调用（EXEC 或 sp_executeSQL）中的 USE 命令组合使用)，并且 USE 的效果只会持续到该批次的持续时间（即直到任何嵌入的 GO）。

然而，我已经从“外部”SQL Server 完成了类似的工作，更新某种“类型”的所有数据库中的特定对象（每个客户端/客户都有一个）。它变得复杂。简要概述：