Monday, April 18, 2011

Tables creation script

Just completed a script which generates "create tables" script.

Yet to add "defaults", "constraints", "indexes"., 'll update the script after I add these as well.

This is just a basic script, it doesn't include identity but it has default constraints added.

I just found its too very complex to get an accurate script because you need to take into account whether the column is marked for replication, whether identity is set on it, if it is computed or not, foreign keys and so on.
I am sorry to leave this unfinished but I lost patience in just trying to correct it/rewrite it, so 'll continue later.
But this script is still treated as a basic one to generate table scripts

Declare @query nvarchar(4000)
Declare @tbl_name varchar(50)
declare @schema varchar(10)
declare @subquery nvarchar(2000)

Declare  Del_arch_tbls cursor for

    Select table_name,table_schema from information_schema.tables where table_type<>'view'
   
Open del_arch_tbls
Fetch next from del_arch_tbls into @tbl_name,@schema

While @@fetch_status=0
begin

    declare @col nvarchar(30)
    declare @data_type nvarchar(10)
    declare @chr_length int
    declare @dt_precision int
    declare @isnull char(4)
    declare @collation nvarchar(40)
    declare @position int
    declare @num_precision int
    declare @col_query nvarchar(2000)
    declare @default varchar(100)
   
    --set @col_query=''
    Declare  cur_col cursor for

    Select column_name,data_type,isnull(character_maximum_length,0),is_nullable,isnull(numeric_precision,0),
    isnull(datetime_precision,0), isnull(collation_name,''),ordinal_position,isnull(column_default,'')
    from information_schema.columns where table_schema= @schema and table_name= @tbl_name
   
    open cur_col
    fetch next from cur_col into  @col,@data_type,@chr_length,@isnull,@num_precision,@dt_precision,
                            @collation,@position,@default
    while @@fetch_status=0
    begin

        -- 1.no need to add precision for datetime, int,smallint and bit, sql won't allow
        -- 2.actual generate script is not showing any collation levels for columns and hence
        -- commenting that code as well

        Set @col_query =  ' [' + @col + ']' + ' [' + @data_type + '] '
            if @chr_length >0
            begin
                set @col_query= @col_query + '('+cast(@chr_length as varchar)+') '
                if len(@default)>0
                    set @col_query= @col_query +  ' default ' + @default

                 --set @col_query= @col_query +  ' collate ' + @collation
            end
            else if @data_type='numeric'
            begin
                set @col_query = @col_query + '('+cast(@num_precision as varchar)+',0)'
                if len(@default)>0
                    set @col_query= @col_query +  ' default ' + @default
            end
--            else if @dt_precision >0
--            begin
--                set @col_query= @col_query + '('+cast(@dt_precision as varchar)+',0)'
--                if len(@default)>0
--                    set @col_query= @col_query +  ' default ' + @default
--            end
            if @isnull='yes'  set @col_query=@col_query + ' null'
            else
                set @col_query=@col_query + ' not null '
           
           
        --print @col_query
        --exec Sp_executesql @query
        --set @col_query=@col_query + ',' + @col_query
       
        if len(@subquery)>0
            set @subquery=@subquery + ',' + @col_query
        else
            set @subquery= @col_query

        fetch next from cur_col into  @col,@data_type,@chr_length,@isnull,@num_precision,@dt_precision,
                            @collation,@position,@default
       
    end
   
    Set @query = 'Create table [' + @schema + '].[' + @tbl_name  + '] (' + @subquery + ') on primary '

    print @query

    Close cur_col
    Deallocate cur_col

    set @subquery=''

    Fetch next from del_arch_tbls into @tbl_name,@schema

end


Close del_arch_tbls
Deallocate del_arch_tbls

No comments:

Post a Comment