2

I am attempting to create multiple tables in multiple databases using declared variables for the db_name, table_name , column_name and column_type. Is this feasible ? I have to create the same tables for 14 databases. The tables are the same for the databases but there are multiple tables and multiple columns.

Sample:

DECLARE @DBName VARCHAR(250) = (SELECT [name] FROM sys.databases WHERE database_id >= 337); --Newly Created Databases
DECLARE @TableName VARCHAR(250) = (SELECT TableName FROM DB.Table.Tables WHERE Enabled = 1);
DECLARE @TableId INT = (SELECT Id FROM DB.Table.Tables WHERE Enabled = 1);
DECLARE @ColumnName VARCHAR(250) = (SELECT ColumnName FROM DB.Table.Columns WHERE Enabled = 1 AND TableId = @TableId);
DECLARE @ColumnType VARCHAR(250) = (SELECT ColumnType FROM DB.Table.Columns WHERE Enabled = 1 AND TableId = @TableId);

USE [master];

CREATE TABLE [@DBName + '.dbo.' + @TableName] (@ColumnName + @ColumnType);
4
  • Hi and welcome to dba.se! You will probably have to create a script with one or more cursors and dynamic sql to script out the create statements. What do you mean with this part: SELECT TableName FROM DB.Table.Tables WHERE Enabled = 1 Do you mean DbName.sys.tables ? What does the WHERE Enabled = 1 stand for? Apr 9 '19 at 18:26
  • Hi, and thank you! The SELECT TableName FROM DB.Table.Tables WHERE Enabled = 1 is referring to a table(ref_table) that has specific tablenames in it , we use that for a replication process. I am trying to only create those tables listed in that table(ref_table) for the new databases. The WHERE Enabled = 1 stands for the table being actively replicated , this is so I only create the active tables. Apr 9 '19 at 18:29
  • Thanks for the clarification! Only using the hardcoded ref_table would be ok then? Apr 9 '19 at 18:33
  • The ref_table itself is acting in a way as a lookup table and I need to use the tables listed in the TableName column as my create table names. I am not sure if I understand correctly. Apr 9 '19 at 18:35
3

You'll need to employ a little semi-advanced dynamic T-SQL for this task.

First, I'll create a small MCVE in tempdb. In future, please create this part yourself when asking questions here :-)

SET NOCOUNT ON;
USE tempdb;
IF SCHEMA_ID(N'Table') IS NOT NULL
BEGIN
    DROP TABLE [Table].Columns;
    DROP TABLE [Table].Tables;
    DROP SCHEMA [Table];
END
GO
CREATE SCHEMA [Table]
CREATE TABLE [Tables]
(
    TableID int NOT NULL
        CONSTRAINT Tables_pk
        PRIMARY KEY CLUSTERED
    , TableName sysname NOT NULL
    , [Enabled] bit NOT NULL
)
CREATE TABLE [Columns]
(
    ColumnID int NOT NULL
        CONSTRAINT Columns_pk
        PRIMARY KEY CLUSTERED
    , TableID int NOT NULL
        CONSTRAINT Columns_TableID
        FOREIGN KEY 
        REFERENCES [Tables](TableID)
    , ColumnName sysname NOT NULL
    , ColumnType nvarchar(150) NOT NULL
    , Nullable bit NOT NULL
    , [Enabled] bit NOT NULL
);
GO

Here, I'm create a single row in [Table].Tables, with two columns a, and b.

INSERT INTO [Table].Tables (TableID, TableName, [Enabled])
VALUES (1, N'a', 1);

INSERT INTO [Table].Columns (ColumnID, TableID, ColumnName, ColumnType, Nullable, [Enabled])
VALUES (1, 1, N'b', N'nvarchar(30)', 1, 1)
    , (2, 1, N'c', N'int', 0, 1)
GO

This is the "interesting" dynamic T-SQL code. It cross joins to sys.databases so that a CREATE TABLE statement is generated for every database on the instance. I'm using WHERE database_id > 5 because I don't have an instance with more than 337 databases - so you'll need to modify that to suit your needs.

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT N'USE ' + QUOTENAME(d.name) + N';CREATE TABLE ' 
    + QUOTENAME(t.TableName) 
    + N'( ' + STUFF((SELECT N', ' 
        + c.ColumnName 
        + N' ' + c.ColumnType 
        + N' ' 
        + CASE WHEN c.Nullable = 1 THEN N'NULL' 
            ELSE N'NOT NULL' 
          END 
    FROM [Table].Columns c 
    WHERE t.TableID = c.TableID 
        AND c.[Enabled] = 1 FOR XML PATH (N'')), 1, 2, N'') + N');'
FROM [Table].Tables t
    CROSS JOIN sys.databases d
WHERE t.[Enabled] = 1
    AND d.database_id > 5;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The code will "print" a CREATE TABLE statement for each database, which you could simply copy-and-paste into a new query window to actually execute. You could replace the PRINT @cmd; with EXEC sys.sp_executesql @cmd; to automatically run each CREATE TABLE statement, however I wouldn't suggest doing that until you're certain this works the way you expect.

Notice, the code above includes the Nullable column - that wasn't in your example code. It's important to declare the nullability for every column when creating a table.

Also, please don't use reserved words for table names; i.e. don't call a table "Table". It's frustratingly hard to type and hard to read later.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.