Tuesday, July 30, 2013

Dynamically Create tables Based on Config Table Data TSQL

I have a table MasterTable defined as follows:
[Table Name],[Field Name]
Both columns are of varchar type.
The first column list a table name and the second a column name. There is a one to many relationship between col 1 and col 2:
Table 1 - Column 1
Table 1 - Column 2
Table 1 - Column 3
Table 2 - Column 1
Table 3 - Column 1
Table 3 - Column 2
For a given table we can have many columns.
The table MasterTable  can have multiple one or more values in the [Table Name] field as well as one or more associated columns in the [Field Name] field.
I need to be able to dynamically create a copy of  tables and associated columns based on the data available in the MaterTable table and make .
Here is the script for dynamically create a copy of  tables and associated columns based on the data available in the MaterTable table
USE [SQL2012]
GO

IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = 'tableconfig'
  )
BEGIN
 DROP TABLE tableconfig;
END
GO

CREATE TABLE TableConfig (
 TableName VARCHAR(100)
 ,ColumnName VARCHAR(100)
 ,ColumnDataType VARCHAR(100)
 )
GO

INSERT INTO TableConfig
VALUES (
 'Table 1'
 ,'Column 1'
 ,'Varchar(100)'
 )
 ,(
 'Table 1'
 ,'Column 2'
 ,'Varchar(100)'
 )
 ,(
 'Table 1'
 ,'Column 3'
 ,'Varchar(100)'
 )
 ,(
 'Table 2'
 ,'Column 1'
 ,'Varchar(100)'
 )
 ,(
 'Table 3'
 ,'Column 1'
 ,'Varchar(100)'
 )
 ,(
 'Table 3'
 ,'Column 2'
 ,'Varchar(100)'
 )
GO

/*SELECT * FROM TableConfig
GO
*/
DECLARE @sqlquery NVARCHAR(4000) = ''
DECLARE @startRow INT = 1;
DECLARE @endRow INT = 1;
DECLARE @startcolumn INT = 1;
DECLARE @endcolumn INT = 1;
DECLARE @tableToCreate VARCHAR(100) = '';
DECLARE @columnName VARCHAR(100) = '';
DECLARE @DataType VARCHAR(100) = '';

WITH MyTables
AS (
 SELECT DISTINCT TableName
 FROM TableConfig
 )
SELECT @endRow = count(*)
FROM MyTables;

/*PRINT @endRow*/
WHILE (@startRow <= @endRow)
BEGIN
 WITH AllTables
 AS (
  SELECT ROW_NUMBER() OVER (
    ORDER BY tablename
    ) AS Rownum
   ,TableName
  FROM TableConfig
  GROUP BY TableName
  )
 SELECT @tableToCreate = TableName
 FROM Alltables
 WHERE Rownum = @startRow

 SET @sqlquery = '';
 SET @sqlquery = @sqlquery + 'IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = ' + '''' + @tableToCreate + '''' + '
  )
 BEGIN
  DROP TABLE ' + '[' + @tableToCreate + ']' + ';' + CHAR(13) + CHAR(10) + 'END' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'CREATE TABLE ' + '[' + @tableToCreate + ']' + '('
 /* PRINT @tableToCreate;
 PRINT @endcolumn
*/
 SET @startcolumn = 1;

 SELECT @endcolumn = count(*)
 FROM TableConfig
 WHERE TableName = @tableToCreate;

 WHILE (@startcolumn <= @endcolumn)
 BEGIN
  WITH Allcolumns
  AS (
   SELECT ROW_NUMBER() OVER (
     ORDER BY columnname
     ) AS Rownum
    ,ColumnName
    ,ColumnDataType
   FROM TableConfig
   WHERE TableName = @tableToCreate
   )
  SELECT @columnName = ColumnName
   ,@DataType = ColumnDataType
  FROM Allcolumns
  WHERE Rownum = @startcolumn

  /* PRINT @columnName
  PRINT @DataType
*/
  SET @sqlquery = @sqlquery + '' + '[' + @columnName + ']' + ' ' + @DataType

  IF @startcolumn < @endcolumn
   SET @sqlquery = @sqlquery + ',';
  SET @startcolumn = @startcolumn + 1;
 END

 /*New Line */
 SET @sqlquery = @sqlquery + ');' + CHAR(13) + CHAR(10) + 'GO';

 PRINT @sqlquery

 SET @startRow = @startRow + 1;
END

IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = 'tableconfig'
  )
BEGIN
 DROP TABLE tableconfig;
END
GO

Output
------------------------
IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = 'Table 1'
  )
BEGIN
 DROP TABLE [Table 1];
END
GO

CREATE TABLE [Table 1] (
 [Column 1] VARCHAR(100)
 ,[Column 2] VARCHAR(100)
 ,[Column 3] VARCHAR(100)
 );
GO

IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = 'Table 2'
  )
BEGIN
 DROP TABLE [Table 2];
END
GO

CREATE TABLE [Table 2] ([Column 1] VARCHAR(100));
GO

IF EXISTS (
  SELECT *
  FROM sys.tables
  WHERE NAME = 'Table 3'
  )
BEGIN
 DROP TABLE [Table 3];
END
GO

CREATE TABLE [Table 3] (
 [Column 1] VARCHAR(100)
 ,[Column 2] VARCHAR(100)
 );
GO

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

Thanks,
Prajesh

Any comments or feedback will be highly appreciated.