Dynamic SQL Query to generate "CREATE Table" script

Rohit 226 Reputation points
2023-10-09T14:11:14.8966667+00:00

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.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,898 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Amira Bedhiafi 16,231 Reputation points
    2023-10-09T17:39:40.5433333+00:00

    Here is my attempt :

    DECLARE @tableName NVARCHAR(256) = 'dbo.test'
    DECLARE @sql NVARCHAR(MAX) = ''
    DECLARE @columnDefinitions NVARCHAR(MAX) = ''
    -- Get column definitions
    SELECT @columnDefinitions += '[' + COLUMN_NAME + '] ' + DATA_TYPE + 
        CASE WHEN DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar') THEN 
            '(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR) END + ')'
        ELSE '' END + ',' + CHAR(10)
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA + '.' + TABLE_NAME = @tableName
    ORDER BY ORDINAL_POSITION
    -- Construct the CREATE TABLE statement
    SET @sql = 'CREATE TABLE ' + @tableName + ' (' + CHAR(10) + @columnDefinitions + ');'
    PRINT @sql
    

    The script will print a very basic "CREATE TABLE" statement, but it doesn't handle primary keys, foreign keys, unique constraints, indexes, or other complex features.

    Please try and tell us :)User's image

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 102.3K Reputation points
    2023-10-09T20:58:42.8933333+00:00

    I've probably written more than one script to do this, but since I don't have it handy, I went to Google, and this looks promising: https://www.mssqltips.com/sqlservertip/3443/script-all-primary-keys-unique-constraints-and-foreign-keys-in-a-sql-server-database-using-tsql/

    If you want to try other hits, my search string was T-SQL script to generate CREATE TABLE with constraints.

    0 comments No comments

  3. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-10-10T02:55:21.07+00:00

    Hi @R-5499

    You can also refer to this thread. It is a bit old, but it is highly discussed.

    https://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query

    Best regards,

    Percy Tang

    0 comments No comments