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?

I got it... if anyone is interested here is the code:::: of course after declaring neccessary variables... Maybe someone else can use this to their advantage!


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