SQL Server组数据库创建脚本

I am trying to create a group of databases (database names should be picked from a table in a database (in same instance) in an incremental order). Can anyone help me how to do this?

我正在尝试创建一组数据库（应该以增量顺序从数据库中的表中（在同一实例中）选择数据库名称）。任何人都可以帮我怎么做？

DECLARE @loopcounter INT = 1, @Maxid INT = 100, @zid nvarchar(100)     

WHILE(@LoopCounter <= @Maxid)
BEGIN
   SELECT @zid = zids
   FROM dbo.list_zid WHERE id = @LoopCounter

   SET @LoopCounter  = @LoopCounter  + 1

   print @zid  
   end

 DECLARE @DBNAME VARCHAR(50)
SET @DBNAME = '@zid'

CREATE DATABASE @DBNAME
GO
ALTER DATABASE @DBNAME SET COMPATIBILITY_LEVEL = 90
GO
ALTER DATABASE @DBNAME SET RECOVERY SIMPLE 
GO

1 个解决方案

#1

So first the warning you will have to use dynamic SQL on text from a table which can leave you open to all kinds of security issues so do NOT do this if you are not 100% certain of the database values!!!! Next you have the gist of it, but you cannot just pass the variable to the statment you want to execute. You have to build the string and then execute the string. See the example for the logic of where and how to do this. I have not shown you how to get the DBName from your table what you have written looks like you can figure that out plus if you don't know that you should NOT do this technique yet!

因此，首先警告您必须对表中的文本使用动态SQL，这会让您对各种安全问题保持开放，所以如果您不是100％确定数据库值，请不要这样做！接下来你有它的要点，但你不能只将变量传递给你想要执行的语句。您必须构建字符串然后执行字符串。请参阅示例，了解在何处以及如何执行此操作的逻辑。我没有告诉你如何从你的表中获取你所写的DBName，如果你不知道你不应该做这个技术，你可以想出这个加号！

DECLARE @loopcounter INT = 1, @Maxid INT = 100, @zid nvarchar(100)     

    WHILE(@LoopCounter <= @Maxid)
    BEGIN
       DECLARE @DBNAME VARCHAR(50)
        SET @DBNAME = '@zid'

       SELECT @zid = zids
       FROM dbo.list_zid WHERE id = @LoopCounter
       --if you are getting the @zid out of the database I would assume you can also get the name on this query too????



        DECLARE @SQLStatements NVARCHAR(MAX)
        SET @SQLStatements = '
    CREATE DATABASE ' + @DBNAME + '
    GO
    ALTER DATABASE ' + @DBNAME + ' SET COMPATIBILITY_LEVEL = 90
    GO
    ALTER DATABASE ' + @DBNAME + ' SET RECOVERY SIMPLE 
    GO'

        EXECUTE (@SQL)


       SET @LoopCounter  = @LoopCounter  + 1
           print @zid  
    END