SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.166 Pro] - Duplicate key columns

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.166 Pro] - Duplicate key columns
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 9.0.166 Pro] - Duplicate key columns Reply with quote
Primary and unique keys have their columns shown with their duplicates in Database Explorer. Check screenshot:



Also the last two columns in the uk_ (id_verzio and id_vonal) are not part of the unique key. As their overlay icon shows, they're part of the foreign keys above.
Wed Oct 26, 2016 10:51 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Thank you very much for reporting this bug. I have logged it under #SA0029978
Wed Oct 26, 2016 11:03 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Would you please provide DDL that can be used to reproduce this issue.

We have tried a few cases and failed to reproduce it so far.
Wed Oct 26, 2016 3:22 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I got it. There are two (or more) schemas having identical table names and structure, the latter including having the same (unique) key names. As soon as I renamed the unique keys, the duplicates went away. The columns that were shown in the unique key were actually part of the unique key in that another schema. I guess SA is only making name matches instead of schema_id and object_id matches. Or something similar.
Wed Oct 26, 2016 3:57 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Ah, that sounds like a different kind of issue. It uses Table Objects query to pull constraints and indexes by parent object id or unique name. That part is correct. But I guess the data is added to the cache and since in SQL Server it constraint names and indexes are not unique across schemas, it leads to incorrect results. Thank you again for spotting this issue.
Wed Oct 26, 2016 5:34 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Hi, here is how you can fix the issue. In the options please replace the code of database query "Table Objects (MSSQL)" with the following

Code:
IF (@@version LIKE '%SQL Server 20%' AND @@version NOT LIKE '%SQL Server 2000%')
or @@version like '%SQL Azure%'

    SELECT d.name, d.[type], c.name, d.definition, 0 as col_pos
    FROM [$DB_NAME$].sys.default_constraints d WITH (NOLOCK)
    LEFT JOIN [$DB_NAME$].sys.[columns] c WITH (NOLOCK) ON
        c.[object_id] = d.parent_object_id AND d.parent_column_id = c.column_id
    WHERE d.parent_object_id = :OBJECT_ID

    UNION ALL

    SELECT d.name, d.[type], c.name, d.definition, 0
    FROM [$DB_NAME$].sys.[check_constraints] d WITH (NOLOCK)
    LEFT JOIN [$DB_NAME$].sys.[columns] c WITH (NOLOCK) ON
        (c.[object_id] = d.parent_object_id) AND d.parent_column_id = c.column_id
    WHERE d.parent_object_id = :OBJECT_ID

    UNION ALL

    SELECT t.name, t.[type], te.type_desc COLLATE SQL_Latin1_General_CP1251_CI_AS, '', 0
    FROM [$DB_NAME$].sys.trigger_events te WITH (NOLOCK)
    LEFT JOIN [$DB_NAME$].sys.triggers t WITH (NOLOCK) ON te.[object_id] = t.[object_id]
    WHERE t.parent_id = :OBJECT_ID

    UNION ALL

    SELECT o.name, o.[type], c.name + ' ' + CASE k.is_descending_key WHEN 0 THEN 'ASC' ELSE 'DESC' END, '', k.index_column_id
    FROM [$DB_NAME$].sys.objects o WITH (NOLOCK),
        [$DB_NAME$].sys.indexes i WITH (NOLOCK),
        [$DB_NAME$].sys.index_columns k WITH (NOLOCK),
        [$DB_NAME$].sys.[columns] c WITH (NOLOCK)
    WHERE i.[object_id] = o.parent_object_id
        AND i.name = o.name
        AND k.[object_id] = i.[object_id]
        AND k.index_id = i.index_id
        AND c.[object_id] = k.[object_id]
        AND c.column_id = k.column_id
        AND i.[object_id] = :OBJECT_ID

    UNION ALL

    SELECT
      fk.name, fk.[type],
      c2.name,
      s1.name + '.' + o1.name + '.' + c1.name,
      fkc.constraint_column_id
    FROM [$DB_NAME$].sys.foreign_keys AS fk WITH (NOLOCK)
      JOIN [$DB_NAME$].sys.foreign_key_columns AS fkc WITH (NOLOCK) ON fkc.constraint_object_id = fk.[object_id]
      JOIN [$DB_NAME$].sys.[columns] AS c1 WITH (NOLOCK) ON c1.[object_id] = fkc.referenced_object_id AND c1.column_id = fkc.referenced_column_id
      JOIN [$DB_NAME$].sys.objects AS o1 WITH (NOLOCK) ON o1.[object_id] = c1.[object_id]
      JOIN [$DB_NAME$].sys.schemas AS s1 WITH (NOLOCK) ON s1.[schema_id] = o1.[schema_id]
      JOIN [$DB_NAME$].sys.[columns] AS c2 WITH (NOLOCK) ON c2.[object_id] = fkc.parent_object_id AND c2.column_id = fkc.parent_column_id
    WHERE fkc.parent_object_id = :OBJECT_ID   
 
    UNION ALL

    SELECT i.name + ' (' + i.type_desc + ', ' +
        CASE i.is_unique WHEN 0 THEN 'NONUNIQUE' ELSE 'UNIQUE' END + ')' COLLATE SQL_Latin1_General_CP1251_CI_AS,
        'I',
        c.name + ' ' + CASE ic.is_descending_key WHEN 0 THEN 'ASC' ELSE 'DESC' END,
        '',
        ic.index_column_id
    FROM [$DB_NAME$].sys.indexes i WITH (NOLOCK),
           [$DB_NAME$].sys.index_columns ic WITH (NOLOCK),
           [$DB_NAME$].sys.[columns] c WITH (NOLOCK)
    WHERE ic.[object_id] = i.[object_id]
           AND ic.column_id = c.column_id
           AND c.[object_id] = i.[object_id]
           AND ic.index_id = i.index_id
           AND i.[object_id] = :OBJECT_ID

    UNION ALL

    SELECT s.name, 'S', c.name, CONVERT(NVARCHAR(20), STATS_DATE(o.[object_id], s.stats_id), 20), sc.stats_column_id
    FROM [$DB_NAME$].sys.stats_columns sc WITH (NOLOCK)
      JOIN [$DB_NAME$].sys.[stats] s WITH (NOLOCK) ON s.stats_id = sc.stats_id AND s.[object_id] = sc.[object_id]
      JOIN [$DB_NAME$].sys.all_objects o WITH (NOLOCK) ON o.[object_id] = sc.[object_id]
      JOIN [$DB_NAME$].sys.all_columns c WITH (NOLOCK) ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
    WHERE o.object_id = :OBJECT_ID

    ORDER BY 2, 1, 5
ELSE

    SELECT o.name, o.xtype, k.name, case when o.xtype = 'TR' then '' else c.[text] end, k.colid
    FROM [$DB_NAME$].dbo.sysobjects o WITH (NOLOCK)
      LEFT JOIN [$DB_NAME$].dbo.syscomments c WITH (NOLOCK) ON o.id = c.id AND c.colid = 1
      LEFT JOIN [$DB_NAME$].dbo.syscolumns k WITH (NOLOCK) ON k.id = o.parent_obj AND k.colid = o.info
    WHERE o.xtype IN ('C', 'PK', 'UQ', 'D', 'TR')
        AND (o.status & 64) = 0 AND o.parent_obj = :OBJECT_ID

    UNION ALL

    SELECT fk.name, fk.xtype,
      (select name from [$DB_NAME$].dbo.syscolumns WITH (NOLOCK) where id = f1.rkeyid and colid = f1.rkey),
      (select u.name + '.' + o.name from [$DB_NAME$].dbo.sysusers u WITH (NOLOCK), [$DB_NAME$].dbo.sysobjects o WITH (NOLOCK) where u.uid = o.uid and o.id = f1.rkeyid) + '.' +
      (select name from [$DB_NAME$].dbo.syscolumns WITH (NOLOCK) where id = f1.fkeyid and colid = f1.fkey),
     f1.keyno
    FROM [$DB_NAME$].dbo.sysforeignkeys f1 WITH (NOLOCK)
        LEFT JOIN [$DB_NAME$].dbo.sysobjects fk WITH (NOLOCK) ON f1.constid = fk.id AND fk.xtype = 'F'
    WHERE fk.parent_obj = :OBJECT_ID

    UNION ALL

    SELECT i.name,
           'I',
           c.name,
           '',
         k.keyno
    FROM [$DB_NAME$].dbo.sysindexes i WITH (NOLOCK),
           [$DB_NAME$].dbo.sysindexkeys k WITH (NOLOCK),
           [$DB_NAME$].dbo.syscolumns c WITH (NOLOCK)
    WHERE k.id = i.id
           AND c.id = i.id
           AND k.indid = i.indid
           AND c.colid = k.colid
           AND i.id = :OBJECT_ID
    ORDER BY 2, 1, 5

Mon Oct 31, 2016 10:11 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.