[SA 9.0.166 Pro] - Duplicate key columns

[SA 9.0.166 Pro] - Duplicate key columns

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.

Thank you very much for reporting this bug. I have logged it under #SA0029978

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.

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.

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.

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