Generate Table Defination 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 query window without switching over to the SSMS object explorer? Here it is, I have created 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 below image.
Source code of script is below.
001.CREATE Proc GetTableCreateScript
002.(@TableName varchar(150))
003.as
004.Begin
005.
006.
007.
008./*
009. Name: Export Table Definition
010. Developer: Nisarg Upadhyay
011. Description:
012. 1.This script will generate Table defination in text format. It will include
013. 1. Table Definition
014. 2. Unique, Default and Primary key Constraint
015. 3. Indexes
016.
017. Version: v1.0
018.*/
019.
020./*Check weather table is present or not*/
021.
022.If not exists (select name from sys.tables where name =@TableName) begin Print 'Table do not exists.' Return end
023.
024./*Prepare temp table to keep entire table defination at one place*/
025.Create Table #TableDefination (s nvarchar(max), id int identity)
026.
027./*Insert Create Table staement*/
028.Insert Into #TableDefination Values ('Create Table ' + @TableName + '(')
029.
030./*Columns*/
031.
032.Insert Into #TableDefination
033.select
034. '['+column_name+'] ' +
035. data_type + case when character_maximum_length=-1 then '(' + 'max' + ')' else coalesce('('+cast(character_maximum_length as varchar)+')','') End + ' ' +
036. 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 inn
037.er 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 + ' ' +','
038.
039. from information_schema.columns where table_name = @TableName
040. order by ordinal_position
041.
042. /*Close bracket*/
043. update #TableDefination set s=left(s,len(s)-1) where id=@@identity
044. insert into #TableDefination(s) values (' )')
045.
046./*Constraint: Default*/
047.
048.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 Constrai
049.nt_Type = 'DEFAULT' AND Col.Table_Name = @TableName)
050.Begin
051. Insert into #TableDefination
052. 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.table
053.s b on a.parent_object_id=b.object_id where b.name=@TableName
054.End
055.
056./*Constraint: Primary Key*/
057.
058.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 Constrai
059.nt_Type = 'PRIMARY KEY' AND Col.Table_Name = @TableName)
060.Begin
061. 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 Co
062.l.Constraint_Name = Tab.Constraint_Name AND Col.Table_Name = Tab.Table_Name AND Constraint_Type = 'PRIMARY KEY' and Tab.Table_name=@TableName
063.End
064.
065./*Constraint: Unique*/
066.
067.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 Constrai
068.nt_Type = 'UNIQUE' AND Col.Table_Name = @TableName)
069.Begin
070. 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.C
071.onstraint_Name = Tab.Constraint_Name AND Col.Table_Name = Tab.Table_Name AND Constraint_Type = 'UNIQUE' and Tab.Table_name=@TableName
072.End
073.
074./*Indexes*/
075.
076. Insert into #TableDefination
077. select
078. 'CREATE '+ i.[type_desc] + ' INDEX [' + I.NAME + '] ON [' + O.NAME + ']
079. ( ' + COLUMNS.Normal +')'
080.from sys.indexes i
081.join sys.objects o on i.object_id = o.object_id
082.cross apply
083.(
084. select
085. substring
086. (
087. (
088. select ', ' + co.[name]
089. from sys.index_columns ic
090. join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
091. where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 0
092. order by ic.key_ordinal
093. for xml path('')
094. )
095. , 3
096. , 10000
097. ) as [Normal]
098. , substring
099. (
100. (
101. select ', ' + co.[name]
102. from sys.index_columns ic
103. join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
104. where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 1
105. order by ic.key_ordinal
106. for xml path('')
107. )
108. , 3
109. , 10000
110. ) as [Included]
111.
112.) Columns
113.where o.[type] = 'U' --USER_TABLE
114.and o.name =@TableName
115.order by o.[name], i.[name], i.is_primary_key desc
116.
117.
118. /*Final Select*/
119.
120. Select s from #TableDefination
121.
122. Drop Table #TableDefination
123.
124.
125.
126.
127. End
Please leave is feedback.