suppose if u need generate create table script of another existing table then you can use the following SP
CREATE Procedure GenerateCreateTableScript (
@tableName varchar(100))
as
BEGIN
If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)
Begin
declare @sql varchar(MAX)
declare @table varchar(100)
declare @cols table (datatype varchar(50))
insert into @cols values('bit')
insert into @cols values('binary')
insert into @cols values('bigint')
insert into @cols values('int')
insert into @cols values('float')
insert into @cols values('datetime')
insert into @cols values('text')
insert into @cols values('image')
insert into @cols values('uniqueidentifier')
insert into @cols values('smalldatetime')
insert into @cols values('tinyint')
insert into @cols values('smallint')
insert into @cols values('sql_variant')
set @sql=''
Select @sql=@sql+
case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +
case when Data_Type in (Select datatype from @cols) then '' else '(' end+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+','+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end
when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end+
case when Data_Type in (Select datatype from @cols)then '' else ')' end+
case when Is_Nullable='No' then ' Not null,' else ' null,' end
from Information_Schema.COLUMNS where Table_Name=@tableName
select @table= 'Create table ' + table_Name from Information_Schema.COLUMNS where table_Name=@tableName
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'
select @sql as DDL
End
Else
Select 'The table '+@tableName + ' does not exist'
END