USING SCRIPT GET TABLE STRUCTURE IN SQL SERVER


Create Procedure GenerateScript (              
@tableName varchar(100))              
as              
If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)              
Begin               
declare @sql varchar(8000)               
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'