使用变量创建模式的脚本

问题描述

我很惊讶以前没有出现过这种情况，但我在这里或其他地方的搜索中都没有找到任何内容.我发现了一些模糊的相似之处，这表明问题在于我的脚本中的 Use 命令仅持续那一行，但没有说明如何解决这个问题.

I'm surprised that this hasn't come up before but I haven't found anything in searches either here or elsewhere. I found something vaguely similar which indicates that the problem is that the Use command in my script lasts only for that one line, but there was no indication of how to work around that.

我想要做的是:创建一个通用脚本来创建一个包含我所有常见模式和表的模板"数据库.所有变量(例如数据库名称)都旨在在标题中设置，以便可以根据需要更改它们，并且脚本可以直接运行，而无需进行任何有风险的搜索和替换操作来更改硬编码值.

What I'm trying to do: Create a generic script to create a "template" database with all of my common schemas and tables. All of the variables (such as the database name) are intended to be set in the header so that they can be changed as needed and the script can be just run without needing to do any risky search and replace operations to change hard coded values.

问题是:我无法在正确的数据库中生成模式；他们都是在Master中生成的.试图显式设置数据库没有帮助；我刚刚收到运行时错误.

What the problem is: I can't get the schemas to generate in the right database; they're all generating in Master. Trying to explicitly set the database didn't help; I just received runtime errors.

我的技能水平:长期访问用户，但仍处于探索 SQL Server 的山脚下.我敢肯定(嗯，希望)这对于更上一层楼的人来说会非常容易.

My skill level: Long time Access user but still in the foothills of exploring SQL Server. I'm sure (well, hoping) this this will be ridiculously easy for someone further up the slope.

有谁知道我怎么做这样的事情?(现有代码如下所示.)

Does anyone know how I can do something like this? (Existing code shown below.)

DECLARE @DBName NVARCHAR(50) = 'TheDBName';

-- Assume that there's a bunch of code to drop and create the database goes here.
-- This code executes correctly.

SET @SQL = 'Use [' + @DBName + ']';

Print @SQL;

EXEC(@SQL);

SET @Counter = 1;

WHILE @Counter <=3

    BEGIN
        SET @SQL = 'CREATE SCHEMA [' +
        CASE @Counter
            WHEN 1 THEN 'Schema1'
            WHEN 2 THEN 'Schema2'
            WHEN 3 THEN 'Schema3'
        END

        SET @SQL = @SQL + '] AUTHORIZATION [dbo]';

        PRINT 'Creating Schemas, ' + @SQL;

        Exec(@SQL);

    SET @Counter = @Counter + 1;
END

推荐答案

use 命令仅更改您所在范围内的当前数据库，并且动态 SQL 在其自己的范围内运行.

The use command only changes the current db in the scope you are in and dynamic SQL runs in a scope of its own.

从 master

declare @SQL nvarchar(max)
set @SQL  = N'use tempdb; print db_name()'
exec(@SQL)
print db_name()

结果:

tempdb
master

试试这个:

DECLARE @DBName NVARCHAR(50) = 'TheDBName';
DECLARE @SQL NVARCHAR(max)
DECLARE @SQLMain NVARCHAR(max)
DECLARE @Counter int

SET @SQLMain = 'Use [' + @DBName + ']; exec(@SQL)';


SET @Counter = 1;

WHILE @Counter <=3
BEGIN
  SET @SQL = 'CREATE SCHEMA [' +
        CASE @Counter
            WHEN 1 THEN 'Schema1'
            WHEN 2 THEN 'Schema2'
            WHEN 3 THEN 'Schema3'
        END

  SET @SQL = @SQL + '] AUTHORIZATION [dbo]';

  EXEC sp_executesql @SQLMain, N'@SQL nvarchar(max)', @SQL;

  SET @Counter = @Counter + 1;
END

这篇关于使用变量创建模式的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！