Priya Jha

Priya Jha

  • NA
  • 8
  • 987

Dynamic SQL Query to generate "CREATE Table" script

Oct 11 2023 10:31 AM

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.


Answers (2)