Dynamic SQL Query to generate "CREATE Table" script

Priya Jha

Hello,

Is there a sql query that can be used to generate the "Create table" script which includes all the keys & constraints on the table in a database.

For eg : I have a table "test" with below create table script

CREATE TABLE dbo.departments (
    department_id INT PRIMARY KEY,
    department_name NVARCHAR(255) NOT NULL
);

-- Create a non-clustered index on department_name column
CREATE INDEX IX_DepartmentName ON dbo.departments (department_name);

CREATE TABLE dbo.test (
    id INT PRIMARY KEY,
    name NVARCHAR(255) UNIQUE,
    age INT,
    department_id INT,
    CONSTRAINT FK_Department FOREIGN KEY (department_id) REFERENCES dbo.departments (department_id)
);

-- Create a non-clustered index on name column
CREATE INDEX IX_TestName ON dbo.test (name);

-- Create a non-clustered index on department_id column
CREATE INDEX IX_TestDepartmentId ON dbo.test (department_id);

Once this is executed in database , we want to write a dynamic sql query to generate "Create table" script for a table existing in the database with all the constraints / keys & indexes, can someone help me with this ?

Thanks for your help in advance.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naveen Kumar2y ago

Ajay Kumar - Your script were failing at multiple places, I have corrected the errors, here is the updated script.

Thank you for the base script.

CREATE PROCEDURE dbo.usp_Generate_Dynamic_Table
	@TableName NVARCHAR(128)
AS
BEGIN
--DECLARE @TableName NVARCHAR(128) = 'test'; -- Specify the table name here
DECLARE @Sql NVARCHAR(MAX) = '';

-- Generate CREATE TABLE script
SET @Sql += 'CREATE TABLE ' + @TableName + ' (' + CHAR(13) + CHAR(10);

-- Column definitions
SELECT
    @Sql += '    ' + COLUMN_NAME + ' ' + DATA_TYPE +
            CASE
                WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(5)) + ')'
                ELSE ''
            END + ' ' +
            CASE
                WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL'
                ELSE 'NULL'
            END + ',' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName;

-- Primary key constraint
SELECT @Sql += '    CONSTRAINT ' + COLUMN_NAME + '_PK PRIMARY KEY (' + COLUMN_NAME + '),' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @TableName AND CONSTRAINT_NAME LIKE 'PK%';

-- Foreign key constraints
SELECT @Sql += '    CONSTRAINT ' + CONSTRAINT_NAME + ' FOREIGN KEY (' + COLUMN_NAME + ') REFERENCES ' +
            TABLE_NAME + '(' + COLUMN_NAME + '),' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @TableName AND CONSTRAINT_NAME LIKE 'FK%';

-- Unique constraints
SELECT @Sql += '    CONSTRAINT ' + CONSTRAINT_NAME + ' UNIQUE (' + COLUMN_NAME + '),' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @TableName AND CONSTRAINT_NAME LIKE 'UQ%';

-- Indexes
SELECT DISTINCT @Sql += '    CREATE INDEX ' + object_name(object_id) + ' ON ' + object_name(object_id) + ' (' +
            STUFF((SELECT DISTINCT ', ' + col_name(object_id,index_column_id) FROM SYS.INDEX_COLUMNS
                   WHERE object_name(object_id) = @TableName AND NAME = outerTable.NAME
                   --ORDER BY key_ordinal
                   FOR XML PATH('')), 1, 2, '') + ');' + CHAR(13) + CHAR(10)
FROM SYS.INDEXES AS outerTable
WHERE object_name(object_id) = @TableName AND NAME NOT LIKE 'PK_%' AND NAME NOT LIKE 'UQ_%';

-- Remove trailing comma and close the CREATE TABLE statement
SET @Sql = LEFT(@Sql, LEN(@Sql) - 3) + CHAR(13) + CHAR(10) + ');';

-- Print the final SQL script
PRINT @Sql;
END

Output of the procedure:

Few are the changes I remember have listed below:

There is no column "REFERENCED_TABLE_NAME" in INFORMATION_SCHEMA.KEY_COLUMN_USAGE table.
There is no table called "INFORMATION_SCHEMA.INDEXES", it is "SYS.INDEXES"
In SYS.INDEXES, there is no column called TABLE_NAME.
In SYS.INDEXES, there is no column called INDEX_NAME.
There is no table called "INFORMATION_SCHEMA.INDEX_COLUMN", it is "SYS.INDEX".

Ajay Kumar2y ago

DECLARE @TableName NVARCHAR(128) = 'test'; -- Specify the table name here
DECLARE @Sql NVARCHAR(MAX) = '';

-- Generate CREATE TABLE script
SET @Sql += 'CREATE TABLE ' + @TableName + ' (' + CHAR(13) + CHAR(10);

-- Column definitions
SELECT 
    @Sql += '    ' + COLUMN_NAME + ' ' + DATA_TYPE + 
            CASE 
                WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(5)) + ')' 
                ELSE '' 
            END + ' ' +
            CASE 
                WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' 
                ELSE 'NULL' 
            END + ',' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @TableName;

-- Primary key constraint
SELECT @Sql += '    CONSTRAINT ' + COLUMN_NAME + '_PK PRIMARY KEY (' + COLUMN_NAME + '),' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = @TableName AND CONSTRAINT_NAME LIKE 'PK%';

-- Foreign key constraints
SELECT @Sql += '    CONSTRAINT ' + CONSTRAINT_NAME + ' FOREIGN KEY (' + COLUMN_NAME + ') REFERENCES ' + 
            REFERENCED_TABLE_NAME + '(' + REFERENCED_COLUMN_NAME + '),' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = @TableName AND CONSTRAINT_NAME LIKE 'FK%';

-- Unique constraints
SELECT @Sql += '    CONSTRAINT ' + CONSTRAINT_NAME + ' UNIQUE (' + COLUMN_NAME + '),' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = @TableName AND CONSTRAINT_NAME LIKE 'UQ%';

-- Indexes
SELECT @Sql += '    CREATE INDEX ' + INDEX_NAME + ' ON ' + TABLE_NAME + ' (' + 
            STUFF((SELECT ', ' + COLUMN_NAME FROM INFORMATION_SCHEMA.INDEX_COLUMNS 
                   WHERE TABLE_NAME = @TableName AND INDEX_NAME = outerTable.INDEX_NAME 
                   ORDER BY ORDINAL_POSITION 
                   FOR XML PATH('')), 1, 2, '') + ');' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.INDEXES AS outerTable 
WHERE TABLE_NAME = @TableName AND INDEX_NAME NOT LIKE 'PK_%' AND INDEX_NAME NOT LIKE 'UQ_%';

-- Remove trailing comma and close the CREATE TABLE statement
SET @Sql = LEFT(@Sql, LEN(@Sql) - 3) + CHAR(13) + CHAR(10) + ');';

-- Print the final SQL script
PRINT @Sql;