0

My script that I've altered from the answer here is making duplicate columns in only SOME of the tables. I can't figure out what the problem is. The purpose of the script is to generate CREATE TABLE statements for already existing tables.

If it matters, I'm using SQL Server.

SELECT 
CREATE TABLE [' + OBJECT_SCHEMA_NAME(so.id) + '].[' + so.name + '](' + o.list + '' + CASE 
            WHEN tc.Constraint_Name IS NULL
                THEN ')'
            ELSE 'CONSTRAINT ' + tc.Constraint_Name + ' PRIMARY KEY NONCLUSTERED ' + ' (' + LEFT(j.List, Len(j.List) - 1) + '))' + 'WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)'
            END
    FROM sysobjects so
    CROSS APPLY (
        SELECT '  [' + column_name + '] ' + data_type + CASE data_type
                WHEN 'sql_variant'
                    THEN ''
                WHEN 'text'
                    THEN ''
                WHEN 'ntext'
                    THEN ''
                WHEN 'xml'
                    THEN ''
                WHEN 'decimal'
                    THEN '(' + cast(numeric_precision AS VARCHAR) + ', ' + cast(numeric_scale AS VARCHAR) + ')'
                ELSE coalesce('(' + CASE 
                            WHEN character_maximum_length = - 1
                                THEN 'MAX'
                            ELSE cast(character_maximum_length AS VARCHAR)
                            END + ')', '')
                END + ' ' + CASE 
                WHEN EXISTS (
                        SELECT id
                        FROM syscolumns
                        WHERE object_name(id) = so.name
                            AND name = column_name
                            AND columnproperty(id, name, 'IsIdentity') = 1
                        )
                    THEN 'IDENTITY(' + cast(ident_seed(so.name) AS VARCHAR) + ',' + cast(ident_incr(so.name) AS VARCHAR) + ')'
                ELSE ''
                END + ' ' + (
                CASE 
                    WHEN UPPER(IS_NULLABLE) = 'NO'
                        THEN 'NOT '
                    ELSE ''
                    END
                ) + 'NULL ' + CASE 
                WHEN information_schema.columns.COLUMN_DEFAULT IS NOT NULL
                    THEN 'DEFAULT ' + information_schema.columns.COLUMN_DEFAULT
                ELSE ''
                END + ', '
        FROM information_schema.columns
        WHERE table_name = so.name
        ORDER BY ordinal_position
        FOR XML PATH('')
        ) o(list)
    LEFT JOIN information_schema.table_constraints tc ON tc.Table_name = so.Name
        AND tc.Constraint_Type = 'PRIMARY KEY'
    CROSS APPLY (
        SELECT '[' + Column_Name + '], '
        FROM information_schema.key_column_usage kcu
        WHERE kcu.Constraint_Name = tc.Constraint_Name
        ORDER BY ORDINAL_POSITION
        FOR XML PATH('')
        ) j(list)
    WHERE xtype = 'U'
        AND name NOT IN ('dtproperties')

These are one of the tables that gets created with this script:

CREATE TABLE [dbo].[Extended]
(
    [SiteNumber] bigint  NOT NULL,
    [SiteNumber] bigint  NOT NULL,
    [TTrID] nvarchar(10)  NULL,
    [TTrID] nvarchar(10)  NULL,
    [FrameID] nvarchar(10)  NULL,
    [FrameID] nvarchar(10)  NULL,
    [LocationID] nvarchar(5)  NULL,
    [LocationID] nvarchar(5)  NULL,
    [TerminalTransactionTypeID] nvarchar(4)  NULL,
    [TerminalTransactionTypeID] nvarchar(4)  NULL,
)

It's only on a few select tables but does anyone know why the columns are being written twice?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
moni
  • 3
  • 4
  • Looks like probably somewhere in your joins. Try taking out all the dynamic code to generate the table scripts, and run just the selects that are giving you dupe column names, and you can narrow it down much easier that way. – Brad Dec 10 '20 at 20:37
  • .....`FROM information_schema.columns WHERE table_name = so.name` if the same table name appears in two different schemas..then columns of the two tables will get all mixed up... – lptr Dec 10 '20 at 22:16
  • EDIT: nevermind! I figured it out. I just needed to add an AND statement afterwards! Seriously, thanks so much. I was looking in all the wrong places. – moni Dec 10 '20 at 22:56
  • ...you could switch to the current sys.xyz views instead of information_schema. sysobjects is for backwards compatibility. sys.objects is the modern one. The same thing that you have fixed with information_schema.columns needs to be done for the part of the query that deals with sys.columns: `SELECT id FROM syscolumns WHERE object_name(id) = so.name`<- only the objectname, in general it is safer to join on object_id (when possible).there is a caveat for information_schema columns: https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql – lptr Dec 10 '20 at 23:12

0 Answers0