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.
  1. aLTER Proc GetTableCreateScript
  2. (@TableName varchar(150))
  3. as
  4. Begin
  8. /*
  9. Name: Export Table Definition
  10. Developer: Nisarg Upadhyay
  11. Description:
  12. 1.This script will generate Table defination in text format. It will include
  13. 1. Table Definition
  14. 2. Unique, Default and Primary key Constraint
  15. 3. Indexes
  17. Version: v1.0
  18. */
  20. /*Check weather table is present or not*/
  22. If not exists (select name from sys.tables where name =@TableName) begin Print 'Table do not exists.' Return end
  24. /*Prepare temp table to keep entire table defination at one place*/
  25. Create Table #TableDefination (s nvarchar(max), id int identity)
  27. /*Insert Create Table staement*/
  28. Insert Into #TableDefination Values ('Create Table ' + @TableName + '(')
  30. /*Columns*/
  32. Insert Into #TableDefination
  33. select
  34. '['+column_name+'] ' +
  35. data_type + case when character_maximum_length=-1 then '(' + 'max' + ')' else coalesce('('+cast(character_maximum_length as varchar)+')','') End + ' ' +
  36. 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 + ' ' +','
  37. from information_schema.columns where table_name = @TableName
  38. order by ordinal_position
  40. /*Close bracket*/
  41. update #TableDefination set s=left(s,len(s)-1) where id=@@identity
  42. insert into #TableDefination(s) values (' )')
  44. /*Constraint: Default*/
  46. 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)
  47. Begin
  48. Insert into #TableDefination
  49. 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
  50. End
  52. /*Constraint: Primary Key*/
  54. 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)
  55. Begin
  56. 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
  57. End
  59. /*Constraint: Unique*/
  61. 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)
  62. Begin
  63. 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
  64. End
  66. /*Indexes*/
  68. Insert into #TableDefination
  69. select
  70. 'CREATE '+ i.[type_desc] + ' INDEX [' + I.NAME + '] ON [' + O.NAME + ']
  71. ( ' + COLUMNS.Normal +')'
  72. from sys.indexes i
  73. join sys.objects o on i.object_id = o.object_id
  74. cross apply
  75. (
  76. select
  77. substring
  78. (
  79. (
  80. select ', ' + co.[name]
  81. from sys.index_columns ic
  82. join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
  83. where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 0
  84. order by ic.key_ordinal
  85. for xml path('')
  86. )
  87. , 3
  88. , 10000
  89. ) as [Normal]
  90. , substring
  91. (
  92. (
  93. select ', ' + co.[name]
  94. from sys.index_columns ic
  95. join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
  96. where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 1
  97. order by ic.key_ordinal
  98. for xml path('')
  99. )
  100. , 3
  101. , 10000
  102. ) as [Included]
  104. ) Columns
  105. where o.[type] = 'U' --USER_TABLE
  106. and o.name =@TableName
  107. order by o.[name], i.[name], i.is_primary_key desc
  110. /*Final Select*/
  112. Select s from #TableDefination
  114. Drop Table #TableDefination
  115. End
Leave your valuable feedback.