DECLARE @COLNAME VARCHAR(100) DECLARE @CREATE VARCHAR (1000) DECLARE @TYPE VARCHAR(1) SET @COLNAME = '' SET @TYPE = 5 SET @CREATE = 'CRATE TABLE TABLE_TYPE_' + @TYPE + '(' DECLARE MyCursor CURSOR FOR SELECT Name FROM LAYOUT WHERE RecordType = @TYPE ORDER BY Start OPEN MyCursor FETCH NEXT FROM MyCursor INTO @COLNAME WHILE @@FETCH_STATUS = 0 BEGIN SET @COLNAME = REPLACE(@COLNAME, 'WX-', '') SET @COLNAME = REPLACE(@COLNAME, '(', '') SET @COLNAME = REPLACE(@COLNAME, ')', '') SET @CREATE = @CREATE + '[' + @COLNAME + ']' + ' VARCHAR(1000),' FETCH NEXT FROM MyCursor INTO @COLNAME END CLOSE MyCursor DEALLOCATE MyCursor SET @CREATE = LEFT(@CREATE, len(@CREATE) -1) + ')' --get rid of last comma PRINT (@CREATE) --EXEC (@CREATE)
It seem like there is the maximum limit on MS. When I print the @CREATE, many rows have been cut off. EX. There are 300 columns but somehow it can only print out up to 200 columns How can I gerenrate the create table statements without any cut offs.
I know some people will suggest don't use the cursor, but since there are so many columns for one table, it will be very time consuming to type out all the columns name.