Now this is whats happening... its creating table a and table b but not table c. The reason for this is because when @@fetch_status =0 it will perform the cursor. So when its on the last row of the recordset (tablec col2 varchar(20) null) there is no @table to pull... so it doesn't go back through the cursor to set the @sql to close out and execute to create that last table... I'm wondering if there is any way around this... I was thinking about simply adding after the cursor closed a statement saying
set @sql = @sql + ")"
to create that last table, but lets say in the event there are no rows in the recordset, and there are no tables to create... sticking that in there will cause an error. And I've attempted to add another while @@fetch_statement <> 0 or =-1 however, that just causes a continuous loop in my code... unless i did that incorrectly. Anyway heres the code. Maybe someone can think of a way for me to determine within the cursor if its the last row then to close it and execute it.
Declare CreateTableCursor Cursor For
Select table_name, column_definition
order by table_name, ordinal_position
--open the cursor
--reset values of variables
Set @prevtable = ''
Set @sql = ''
Fetch Next from CreateTableCursor into @table, @coldef --fetch row's values into those variables
While @@fetch_status = 0
If object_id(@table) is null
If @prevtable <> @table
If @sql <> ''
Set @sql = @sql + " )" --This statement closes out the @sql string and the create
exec(@sql) --table string executes, creating the table
Set @sql = "Create table dbo." + @table + "( " + @coldef
If @prevtable = @table
set @sql = @sql + ", "
set @sql = @sql + @coldef
set @prevtable = @table
Fetch next from createtablecursor into @table, @coldef