Generate a create table query of existing table in sql server

To generate a CREATE TABLE query for an existing table in SQL Server, you can use the following approach:

Using SQL Server Management Studio (SSMS):

Right-click the table in Object Explorer.
Select "Script Table as" -> "CREATE To" -> "New Query Editor Window" or "File".
Using T-SQL Script: You can use the INFORMATION_SCHEMA views or sys catalog views to script out the table creation. Here's an example query to help you generate the CREATE TABLE statement manually:

 

DECLARE @TableName NVARCHAR(128) = 'YourTableName';
DECLARE @SQL NVARCHAR(MAX) = '';

-- Generate column definitions
SELECT @SQL = @SQL + '    ' + COLUMN_NAME + ' ' + DATA_TYPE +
    CASE
        WHEN DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') THEN '(' +
            CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE
            CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR) END + ')'
        WHEN DATA_TYPE IN ('decimal', 'numeric') THEN '(' +
            CAST(NUMERIC_PRECISION AS NVARCHAR) + ',' +
            CAST(NUMERIC_SCALE AS NVARCHAR) + ')'
        ELSE ''
    END + ' ' +
    CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END + ', ' + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName;

-- Remove trailing comma
SET @SQL = LEFT(@SQL, LEN(@SQL) - 2);

-- Add table creation statement
SET @SQL = 'CREATE TABLE ' + @TableName + CHAR(10) + '(' + CHAR(10) + @SQL + CHAR(10) + ');';

-- Output the result
PRINT @SQL;

SQL

Copy

Replace 'YourTableName' with the name of your table.

Using SQL Server Management Objects (SMO): If you have access to SQL Server's SMO, you can use it to script the table creation programmatically.

This query builds a basic CREATE TABLE statement with column definitions. It may not include all table options, constraints, indexes, or other features. For a complete script, especially for tables with complex structures, SSMS or SMO is recommended.