I get the an error when I execute the query below. what I'm doing wrong?
Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
select
so.name 'Table Name'
,so.id 'Table ID'
,so.xtype
,sc.name 'Column Name'
,sc.id 'Column ID'
,sf.constid
,sf.fkeyid 'Object ID of the table with FOREIGN KEY'
,sf.rkeyid 'Referenced Table ID'
,(select o.name 'Referenced Table'
from sysforeignkeys f
inner join sysobjects o
on o.id=f.rkeyid
where o.xtype='U')
from sysobjects so
inner join syscolumns sc
on so.id=sc.id
inner join sysforeignkeys sf
on so.id=sf.fkeyid
where so.xtype='U'
and (sc.name like 'SSN'
OR sc.name LIKE 'ssn%'
OR sc.name LIKE 'ssn%'
OR sc.name LIKE '%_ssn%'
OR sc.name LIKE '_ocsecno'
OR sc.name LIKE 'Ssn%');
I don't think your subquery is correct as it has no way of referencing your sysobjects aliased "so". Try this instead. Also I don't think you need such a long where clause.
select so.name [Table Name]
,so.id [Table ID]
,so.xtype
,sc.name [Column Name]
,sc.id [Column ID]
,sf.constid
,sf.fkeyid [Object ID of the table with FOREIGN KEY]
,sf.rkeyid [Referenced Table ID]
,zz.name [Reference Table]
from sysobjects so
inner join syscolumns sc on so.id = sc.id
inner join sysforeignkeys sf on so.id = sf.fkeyid
--Use a join here for the reference table column
inner join sysobjects zz on zz.id = sf.rkeyid
where so.xtype='U'
AND(
sc.name LIKE '%ssn%'
OR sc.name LIKE '_ocsecno'
)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments