Generate table definitions using TSQL – SQL Server

We are very much familiar with a system stored procedure called sp_helptext, which gets us the object definition on query window itself, but that sp is limited to procedures, functions, triggers, computed columns, views etc. So far there are no system procedures available in SQL Server to generate table definitions using TSQL including constraints, defaults and indices. The only way to get the table definition is using SSMS GUI either by right clicking on the table or using “Generate Scripts” option under the database context.

Don’t you like to have a stored procedure handy like sp_helptext to generate table definitions for you from query window without switching over to the SSMS object explorer? Here it is, I have created stored procedure called sp_helptable. Using this procedure you can get the table definitions including primary key, foreign key constraints, check and default constraints, unique and non-clustered indexes on the query window itself like sp_helptext.

sp_helptable

Leave a feedback 🙂

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

10 thoughts on “Generate table definitions using TSQL – SQL Server

  • November 11, 2015 at 11:42 pm
    Permalink

    Very helpful. Thank you Prasad

    Reply
  • April 4, 2016 at 2:32 pm
    Permalink

    Thanks, very useful, saved me a lot of time.
    I made 2 improvements I needed for my use:

    1. I noticed that if a column is deleted from the database, the ORDINAL_POSITION is changed respectively but the id of the column remains so I had to change:

    SELECT
    @MaxColOrder = MAX(ORDINAL_POSITION)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @tableName AND TABLE_SCHEMA = @schemaName

    to

    SELECT @MaxColOrder = MAX (Column_id)
    FROM INFORMATION_SCHEMA.TABLES inf_tbl
    INNER JOIN sys.columns sys_col
    ON inf_tbl.TABLE_NAME = OBJECT_NAME(sys_col.object_id)
    WHERE inf_tbl.TABLE_NAME = @tableName AND inf_tbl.TABLE_SCHEMA = @schemaName

    2. I had a table with a column defined with a different Collation other than default DB Collation.
    I saved the default DB collation to a variable, and for each column that had a collation defined I added the collation setting if it was different from the default DB.

    DECLARE @Default_DB_Collation sysname
    SELECT @Default_DB_Collation = collation_name FROM sys.databases where name = DB_NAME()

    + SPACE(1) + CASE
    WHEN sys_col.is_identity = 1 THEN (SELECT TOP 1
    ‘IDENTITY(‘ + CAST(ISNULL(sys_ident.seed_value, 0) AS varchar(10)) + ‘, ‘ + CAST(ISNULL(sys_ident.increment_value, 1) AS varchar(10)) + ‘)’
    FROM sys.identity_columns sys_ident
    WHERE sys_ident.name = sys_col.name AND sys_col.object_id = sys_ident.object_id AND sys_col.is_identity = 1)
    ELSE ”
    END
    + SPACE(1) + CASE
    WHEN sys_col.collation_name IS NOT NULL AND sys_col.collation_name != @Default_DB_Collation THEN ‘COLLATE ‘ + sys_col.collation_name ELSE ”
    END
    + SPACE(1) + CASE
    WHEN sys_col.is_nullable = 1 THEN ‘NULL’ ELSE ‘NOT NULL’
    END + CASE
    WHEN @MaxColOrder = sys_col.column_id THEN ” ELSE ‘, ‘
    END AS cols,

    Reply
    • April 5, 2016 at 10:10 am
      Permalink

      Thanks much for your inputs. I will incorporate these two changes into the main script.

      Reply
  • August 12, 2016 at 10:51 pm
    Permalink

    Works well, can you also add a section to create a SCHEMAS. Otherwsise when you run the script it breaks ( Because the schemas are not present )

    Reply
  • August 13, 2016 at 12:25 am
    Permalink

    BTW I thought I’d share this with you… The SQL for stored procedures are very straightforward. You can obtain them by running the following:

    select [definition] AS ProcDef from sys.sql_modules

    Reply
  • October 15, 2016 at 3:07 am
    Permalink

    It is really commendable work… !!! so much helpful

    Reply
  • November 21, 2016 at 4:42 pm
    Permalink

    How can I save the result of the procedure exection into a table? Or How can I make use of the table script column of the result?

    Reply
    • November 23, 2016 at 12:39 am
      Permalink

      You can simply write like below to have stored proc data set inserted into a table but you need to make sure that the result set data types are matching and expected columns are available in the table to cater the resultset.

      INSERT INTO #table (cols)
      exec usp_procedure @paramters

      Reply
  • March 8, 2017 at 10:12 pm
    Permalink

    Thanks, Prasad, for this script. It works great for tables in the “dbo” schema, but for tables in other schemas, I don’t get the default constraints. The indexes column is populated, as well as he TableScript. Easy fix?

    Reply
  • March 8, 2017 at 10:52 pm
    Permalink

    Never mind! I found the issue. The “WHERE is_system_named = 0” clause for default constraints was filtering-out my table and so, removing that worked. Can you tell me what that column means?

    Reply

Leave a Reply

%d bloggers like this: