Results 1 to 2 of 2

Thread: Recordset

  1. #1
    Join Date
    Jul 2003
    Posts
    37

    Unanswered: Recordset

    Is this possible... I have a recordset with information like this


    TableName ColumnDefinition
    table1 col1 varchar(20) Null
    table1 col2 varchar(10) NotNull
    table2 col1 varchar(10) Null
    table2 col2 varchar(30) NotNull

    I want to build a cursor that will build tables with the table name in the tablename column and with columns defined as such in the column ColumnDefinition.

    Obviously I can't just run through each row as itself because I have multiple columns for a same table... anybody have any ideas how to approach this?

  2. #2
    Join Date
    Jul 2003
    Posts
    37

    Re: Recordset

    Originally posted by justastef
    Is this possible... I have a recordset with information like this


    TableName ColumnDefinition
    table1 col1 varchar(20) Null
    table1 col2 varchar(10) NotNull
    table2 col1 varchar(10) Null
    table2 col2 varchar(30) NotNull

    I want to build a cursor that will build tables with the table name in the tablename column and with columns defined as such in the column ColumnDefinition.

    Obviously I can't just run through each row as itself because I have multiple columns for a same table... anybody have any ideas how to approach this?
    I got it... if anyone is interested here is the code of course this is after declaring neccessary variables::::

    Declare CreateTableCursor Cursor For
    Select table_name, column_definition
    from dbo.db_table_information
    order by table_name, ordinal_position

    open CreateTableCursor

    Set @prevtable = ''
    Set @sql = ''

    Fetch Next from CreateTableCursor into @table, @coldef

    While @@fetch_status = 0
    BEGIN
    If @prevtable <> @table
    BEGIN
    If @sql <> ''
    BEGIN
    Set @sql = @sql + " )"
    exec(@sql)
    END
    Set @sql = "Create table dbo." + @table + "( " + @coldef

    END

    If @prevtable = @table
    BEGIN
    set @sql = @sql + ", "
    set @sql = @sql + @coldef

    END
    set @prevtable = @table
    Fetch next from createtablecursor into @table, @coldef

    END
    Close CreateTableCursor
    Deallocate CreateTableCursor

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •