Generate SQL Script

--This procedure will generate the structure of the table

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


Select 'The table '+@tableName + ' does not exist'           

Published Monday, August 27, 2007 4:23 PM by Madhivanan


No Comments