SQL Server Performance

Generate SQL Script

Discussion in 'Contribute Your SQL Server Scripts' started by Madhivanan, Jul 29, 2005.

  1. Madhivanan Moderator


    --This procedure will generate the Structure of a table

    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 ' Null,' else ' Not 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'



    Madhivanan

    Failing to plan is Planning to fail
  2. dineshasanka Moderator

Share This Page