Generate Table Definition In SQL Server Without GUI

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

How to Use it.

Open SQL Server management studio, select appropriate database and create “GetTableCreateScript" stored procedure. After creating procedure, run the stored procedure as mentioned in the below image.


Below is entire script. 

aLTER Proc GetTableCreateScript
(@TableName varchar(150))
as
Begin



/*
    Name: Export Table Definition
    Developer: Nisarg Upadhyay
    Description:
                    1.This script will generate Table defination in text format. It will include
                            1. Table Definition
                            2. Unique, Default and Primary key Constraint
                            3. Indexes

    Version: v1.0
*/

/*Check weather table is present or not*/

If not exists (select name from sys.tables where name =@TableName) begin Print 'Table do not exists.' Return end

/*Prepare temp table to keep entire table defination at one place*/
Create Table #TableDefination (s nvarchar(max), id int identity)

/*Insert Create Table staement*/
Insert Into #TableDefination Values ('Create Table ' + @TableName + '(')

/*Columns*/

Insert  Into #TableDefination
select
        '['+column_name+'] ' +
        data_type + case when character_maximum_length=-1 then '(' + 'max' + ')' else coalesce('('+cast(character_maximum_length as varchar)+')','') End + ' ' +
        case when exists (  select a.name from sys.identity_columns a inner join sys.tables b on a.object_id=b.object_id and b.name=@TableName and a.name=COLUMN_NAME ) then    'IDENTITY(' +  (    select convert(varchar,seed_value)  from sys.identity_columns a inner join sys.tables b on a.object_id=b.object_id and b.name=@TableName) + ','  + (  select convert(varchar,increment_value)  from sys.identity_columns a inner join sys.tables b on a.object_id=b.object_id and b.name=@TableName) + ')' else '' end +     ' ' +','
 from information_schema.columns where table_name = @TableName
 order by ordinal_position

 /*Close bracket*/
    update #TableDefination set s=left(s,len(s)-1) where id=@@identity
    insert into #TableDefination(s) values ('  )')

/*Constraint: Default*/

if exists (SELECT Col.Column_Name,Tab.CONSTRAINT_NAME from  INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col WHERE     Col.Constraint_Name = Tab.Constraint_Name   AND Col.Table_Name = Tab.Table_Name    AND Constraint_Type = 'DEFAULT'    AND Col.Table_Name = @TableName)
Begin
    Insert into #TableDefination
    Select 'Alter Table [' + @TableName + '] Add Constraint [' + a.Name+'] DEFAULT '+ definition +' FOR ['+ (select name from sys.columns where object_id=b.object_id and column_id=a.parent_column_id ) +']' from sys.default_constraints a  inner join sys.tables b on a.parent_object_id=b.object_id where b.name=@TableName
End

/*Constraint: Primary Key*/

if exists (SELECT Col.Column_Name,Tab.CONSTRAINT_NAME from  INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col WHERE     Col.Constraint_Name = Tab.Constraint_Name   AND Col.Table_Name = Tab.Table_Name    AND Constraint_Type = 'PRIMARY KEY'    AND Col.Table_Name = @TableName)
Begin
    Insert into #TableDefination Select 'Alter Table [' + @TableName + '] Add Constraint [' + Tab.CONSTRAINT_NAME+'] Primary Key ('+ Col.COLUMN_NAME +')' from INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col  where Col.Constraint_Name = Tab.Constraint_Name   AND Col.Table_Name = Tab.Table_Name    AND Constraint_Type = 'PRIMARY KEY' and Tab.Table_name=@TableName
End

/*Constraint: Unique*/

if exists (SELECT Col.Column_Name,Tab.CONSTRAINT_NAME from  INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col WHERE     Col.Constraint_Name = Tab.Constraint_Name   AND Col.Table_Name = Tab.Table_Name    AND Constraint_Type = 'UNIQUE'    AND Col.Table_Name = @TableName)
Begin
        Insert into #TableDefination Select 'Alter Table [' + @TableName + '] Add Constraint [' + Tab.CONSTRAINT_NAME+'] UNIQUE ('+ Col.COLUMN_NAME +')' from INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col  where Col.Constraint_Name = Tab.Constraint_Name   AND Col.Table_Name = Tab.Table_Name    AND Constraint_Type = 'UNIQUE' and Tab.Table_name=@TableName
End

/*Indexes*/

 Insert into #TableDefination
  select
   'CREATE '+ i.[type_desc] + ' INDEX [' + I.NAME + '] ON [' + O.NAME + ']
   ( ' + COLUMNS.Normal  +')'
from sys.indexes i
join sys.objects o on i.object_id = o.object_id
cross apply
(
    select
        substring
        (
            (
                select ', ' + co.[name]
                from sys.index_columns ic
                join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
                where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 0
                order by ic.key_ordinal
                for xml path('')
            )
            , 3
            , 10000
        )    as [Normal]
        , substring
        (
            (
                select ', ' + co.[name]
                from sys.index_columns ic
                join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
                where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 1
                order by ic.key_ordinal
                for xml path('')
            )
            , 3
            , 10000
        )    as [Included]

) Columns
where o.[type] = 'U' --USER_TABLE
and o.name =@TableName
order by o.[name], i.[name], i.is_primary_key desc


 /*Final Select*/

 Select s from #TableDefination

 Drop Table #TableDefination
End

Leave your valuable feedback.