Dynamic SQL Query to generate "CREATE Table" script

Question

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.

Naveen Kumar · Answer

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_idindex_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 Kumar · Answer

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;