Construct your Own create table script

				
--**************************************
-- Name: Construct your Own create table script
-- Description:The code gets the necessary info from information
schema to contsruct a CREATE table statement
existing in the current database
-- By: Eli Leiba
--
-- Inputs:@tableName varchar(50)
--
-- Returns:The create table statemant 
(Does not include indexes,constraints,triggers)
Only the structure!
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=380&lngWId=5--for details.--**************************************

Create proc sp_gen_create_table (@tableName varchar(50))
as
-- Create Table Generation SCript.
-- By : eli leiba 
-- Date 14/8/2001 
-- Desc : How to create a basic Table Create Statement
--like is Generate SQL SCRIPT option
set nocount on 
declare @Num_rows smallint
Create Table #scriptFile 
(LineId int not null identity(1,1) 
 primary key clustered,
 LineText varchar(150) ,
 Ordinal_Position smallint default 0,
 char_Len varchar(10),
 col_default varchar(20),
 Nullable char(10))
Insert into #scriptFile (LineText)
 values ('CREATE TABLE ' + @tableName + '(' )
Insert into #ScriptFile (LineText,
Ordinal_Position,
char_Len,
col_default,
Nullable ) 
 select '	' + Column_Name + ' ' + Data_Type as LineStart ,
 Ordinal_Position,
 case Data_type 
when 'char' then '(' + cast (Character_Maximum_Length as varchar) + ')'
when 'nchar' then '(' + cast (Character_Maximum_Length as varchar) + ')'
when 'nvarchar' then '(' + cast (Character_Maximum_Length as varchar) + ')'
when 'varchar' then '(' + cast (Character_Maximum_Length as varchar) + ')'
else ' ' 
 end
 Character_Maximum_Length,
 case Isnull(column_default,'*')
when '*' then ' ' 
else ' DEFAULT ' + column_default
 end ,
 case Is_Nullable
when 'No' then 'NOT NULL'
when 'Yes' then 'NULL' 
 end 
from Information_schema.columns
where table_name = @tableName 
order by 
Ordinal_Position
Update #ScriptFile 
set LineText = LineText + ' ' + 
char_Len + ' ' + 
Nullable + col_default + ',' 
where Ordinal_Position > 0
select @num_rows = count(*) from #ScriptFile
update #ScriptFile
 set LineText = Replace (LineText,',',')')
where LineId = @num_rows
select LineText from #ScriptFile 
go