How to Show the Correct Column Name and Data Type in T-SQL?

Question

I produced DDLs to create tables on Oracle using T-SQL since my source tables are in SQL Server. While I was using only 4 tables for testing, it produced 4 DDLs but it repeated the name of the last column and the data type of the table even though The number of columns is correct. For example, TABLE_01 has 6 columns and it repeats the last column name and data type for 6 times.

CREATE TABLE  TABLE_01(
ISKEY   INT
ISKEY   INT
ISKEY   INT
ISKEY   INT
ISKEY   INT
ISKEY   INT
);

Here is my code. Does anyone find the reason why it repeats them? I couldn't find the cause. Please help me to solve this problem. I would appreciate your taking time and sharing knowledge for me.

DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('TABLE_01')
INSERT INTO @MyList VALUES ('TABLE_02')
INSERT INTO @MyList VALUES ('TABLE_03')
INSERT INTO @MyList VALUES ('TABLE_04')

DECLARE @VALUE VARCHAR(50)

DECLARE @COLNAME VARCHAR(50) = ''
DECLARE @COLTYPE VARCHAR(50) = ''

DECLARE @COLNUM INT = 0
DECLARE @COL_COUNTER INT = 0

DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @MyList)

-- Loop for Multiple Tables
WHILE @COUNTER < @MAX
BEGIN
SET @VALUE = (SELECT VALUE FROM
      (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) [index] , Value from @MyList) R 
       ORDER BY R.[index] OFFSET @COUNTER 
       ROWS FETCH NEXT 1 ROWS ONLY);

        SELECT  CONCAT('CREATE TABLE  ' , REPLACE(UPPER(@VALUE), '_',''), '(')
        PRINT 'CREATE TABLE  ' + REPLACE(UPPER(@VALUE), '_','') + '('
    
        SET @COLNUM = 0
        SET @COL_COUNTER = 0

        ;WITH numcol AS
        (
        select schema_name(tab.schema_id) as schema_name,
                tab.name as table_name, 
                col.column_id,
                col.name as column_name, 
                t.name as data_type,    
                col.max_length,
                col.precision
         from sys.tables as tab
                inner join sys.columns as col
                on tab.object_id = col.object_id
                left join sys.types as t
                on col.user_type_id = t.user_type_id
                where schema_name(tab.schema_id) = 'dbo' AND tab.name = @VALUE
        )
        SELECT @COLNUM = COUNT(*) OVER (PARTITION BY schema_name, table_name) FROM numcol

        -- Loop for Multiple Columns
        WHILE @COL_COUNTER < @COLNUM
        BEGIN

        SET @COLNAME = ''
        SET @COLTYPE = ''

        SELECT @COLNAME = REPLACE(UPPER(COL.name), '_',''), @COLTYPE = CASE WHEN UPPER(col_type.name) = 'MONEY' THEN '  ' +'    NUMBER(19,4)'
                                                            WHEN UPPER(col_type.name) = 'REAL' THEN '   ' +'    FLOAT(23)'
                                                            WHEN UPPER(col_type.name) = 'FLOAT' THEN '  ' +'    FLOAT(49)'
                                                            WHEN UPPER(col_type.name) = 'NVARCHAR' THEN '   ' +'    NCHAR'
                                                            ELSE '  ' + UPPER(col_type.name)
                                                            END
                                                            
        FROM sys.columns COL
             INNER JOIN sys.tables TAB
             On COL.object_id = TAB.object_id
             left join sys.types as col_type
             on col.user_type_id = col_type.user_type_id
        WHERE OBJECT_NAME(TAB.object_id) = @VALUE
        
        PRINT @COLNAME + @COLTYPE 
        SET @COL_COUNTER = @COL_COUNTER + 1

        END 
        PRINT ');'
SET @COUNTER = @COUNTER + 1

END

Thank you.

Answer 1

That' a really horrible way to create an SQL script. It's effectively a cursor, and more difficult to write, it's also completely unnecessary.

You can just build the whole thing in one go using STRING_AGG.

I make no comment on the validity of the result for Oracle, as I don't know Oracle well enough.

DECLARE @table sysname = 'YourTable';
DECLARE @schema sysname = 'dbo';


DECLARE @columns nvarchar(max);
SELECT @columns = STRING_AGG(CONCAT(
    '  '
    REPLACE(UPPER(col.name), '_', ''),
    '  ',
    CASE UPPER(col_type.name)
      WHEN 'MONEY' THEN '    NUMBER(19,4)'
      WHEN 'REAL' THEN '     FLOAT(23)'
      WHEN 'FLOAT' THEN '    FLOAT(49)'
      WHEN 'NVARCHAR' THEN '     NCHAR'
      ELSE UPPER(col_type.name)
      END,
  ), ',
')
FROM sys.columns col
INNER JOIN sys.tables tab ON col.object_id = tab.object_id
JOIN sys.types as col_type ON col.user_type_id = col_type.user_type_id
JOIN sys.schemas sch ON sch.schema_id = tab.schema_id
WHERE tab.name = @table
  AND sch.name = @schema;



SELECT
  CONCAT(
    'CREATE TABLE ',
    REPLACE(UPPER(@table), '_',''),
    ' (
',
    @columns,
    '
)'
  );