Finding Duplicate Foreign Keys

Hi

i tried the following query and able to get the list of foreign keys with column names as well as referred tables and referenced column

select parent_column_id as 'child Column',object_name(constraint_object_id)as 'FK Name',object_name(parent_object_id) as 'parent table',name,object_name(referenced_object_id)as 'referenced table',referenced_column_id
from sys.foreign_key_columns inner join sys.columns on (parent_column_id = column_id and parent_object_id=object_id)
Order by object_name(parent_object_id) asc

but i am not able to get the fks created more than once on same column refering to same pk

Thanks in AdvanceIs this helpful?

Field1 has the following values;
1, 2, 3, 3, 4, 5, 6, 6, 6, 7, 8, 9
duplicates highlighted in bold

The following SQL will pick out duplicates only

SELECT Field1
FROM MyTable
GROUP BY Field1
HAVING Count(*) > 1

Field1
--
3
6

:cool:|||I am not sure what is the exact problem, but I think 'Information_Schema' might help. It contains number of views that can be used to retrive meta data.

E.g.
Select * from information_Schema.TABLE_CONSTRAINTS|||here's one that I use in SqlSpec, it may be more than you need though:

select
quotename(su2.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so2.name) collate Latin1_General_CI_AS as parent
,quotename(su1.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so1.name) collate Latin1_General_CI_AS as name
,so1.name as shortname ,sc2.name as colName
,'foreign key from ' + quotename(su2.name) + N'.' + quotename(so2.name) + ' to ' + quotename(su3.name) + N'.' + quotename(so3.name) as description
,quotename(su3.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so3.name) collate Latin1_General_CI_AS as refTable
,sc3.name as refColumn
,ObjectProperty(sf.constid, 'CnstIsDisabled') as isDisabled
,ObjectProperty(sf.constid, 'CnstIsNotRepl') as isNotForReplication
,ObjectProperty(sf.constid, 'CnstIsDeleteCascade') as isDeleteCascade
,ObjectProperty(sf.constid, 'CnstIsUpdateCascade') as isUpdateCascade
,so1.object_id as id from sysforeignkeys sf
join sys.objects so1 on sf.constid=so1.object_id
join sys.objects so2 on sf.fkeyid=so2.object_id
join sys.objects so3 on sf.rkeyid=so3.object_id
join sys.schemas su1 on su1.schema_id=so1.schema_id
join sys.schemas su2 on su2.schema_id=so2.schema_id
join sys.schemas su3 on su3.schema_id=so3.schema_id
join syscolumns sc2 on sc2.colid=sf.fkey and sc2.id=so2.object_id
join syscolumns sc3 on sc3.colid=sf.rkey and sc3.id=so3.object_id|||Thanks You ALL for your reply.

i got that using
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

with CTEsql

Finding columns from sysindexes.

I understand that in the "Keys" column in the "sysindexes" table, the columns are stored in Binary format. Is there any way by which I can get these column names by deciphering sysindexes.keys ? I don't want to use index_col() function.Use sysindexes, syscolumns, sysindexkeys. See BOL for details.|||Thanks a lot. I used your suggestion and got the results. I had forgotten about the sysindexkeys table initially. Thanks again. --Suresh.

Find the Forign keys's primary key table

I hava a table, called Items which has 2 foriegn keys. i want to find the
forign keys's primary key table through sql query.
Please anybody help me.
Regards,
R.SathiamoorthyIf you're using SQL Server 2005 you can piece together the info from
the sys.foreign_keys and sys.foreign_key_columns views.
object_id in sys.fk is the object_id of the constraint
parent_object_id in sys.fk is the table that the constraint is created
in
constraint_object_id in sys.fkc relates to object_id in sys.fk
parent_object_id in sys.fkc is the table that the constraint is
created in
parent_column_id is the column on which the constraint is created
referenced_object_id is the table the constraint is referencing
referenced_column_id is the column in the referenced table that is
used in the constraint.
The joining to sys.tables and sys.columns will get you the info you
require.
Cheers,
Jason Lepack
On Sep 7, 6:05 am, "Sathiamoorthy" <some...@.microsoft.com> wrote:
> I hava a table, called Items which has 2 foriegn keys. i want to find the
> forign keys's primary key table through sql query.
> Please anybody help me.
> Regards,
> R.Sathiamoorthy|||I though this was an intresting question, so I worte a little code. Sorry I
didn't have time to finish it, gotta go to work.
-- Create and use a NEW, empty database.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[tab2]') AND type in (N'U'))
DROP TABLE [dbo].[tab2]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[tab1]') AND type in (N'U'))
DROP TABLE [dbo].[tab1]
CREATE TABLE tab1 (col1 INT, CONSTRAINT PK_tab1 PRIMARY KEY(col1))
CREATE TABLE tab2 (
col1 INT,
col2 INT,
CONSTRAINT PK_tab2 PRIMARY KEY(col1, col2),
CONSTRAINT FK_tab2_tab1 FOREIGN KEY (col1) REFERENCES tab1(col1)
)
SELECT so1.[name], so1.id, so2.name, so2.id, so2.xtype, so2.type, *
FROM sysobjects so1
INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
WHERE so1.[name] IN ('tab1', 'tab2')
SELECT sc.*, 'XXX' xxx, so.* FROM sysconstraints sc INNER JOIN sysobjects so
ON so.id = sc.id
DECLARE @.HasFK SYSNAME
SELECT @.HasFK = name
from sysobjects
where id in (
SELECT fkeyid
FROM sysreferences sr
WHERE constid IN (
SELECT so2.id
FROM sysobjects so1
INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
)
)
DECLARE @.References SYSNAME
SELECT @.References = name
from sysobjects
where id in (
SELECT rkeyid
FROM sysreferences sr
WHERE constid IN (
SELECT so2.id
FROM sysobjects so1
INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
)
)
SELECT @.HasFK + ' has FK to ' + @.References
"Sathiamoorthy" <someone@.microsoft.com> wrote in message
news:uhuElgT8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>I hava a table, called Items which has 2 foriegn keys. i want to find the
> forign keys's primary key table through sql query.
> Please anybody help me.
> Regards,
> R.Sathiamoorthy
>|||See my post 'sqlschema'
"Jay" <spam@.nospam.org> wrote in message
news:%23gwFK8V8HHA.1208@.TK2MSFTNGP03.phx.gbl...
>I though this was an intresting question, so I worte a little code. Sorry I
>didn't have time to finish it, gotta go to work.
> -- Create and use a NEW, empty database.
> IF EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[tab2]') AND type in (N'U'))
> DROP TABLE [dbo].[tab2]
> IF EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[tab1]') AND type in (N'U'))
> DROP TABLE [dbo].[tab1]
> CREATE TABLE tab1 (col1 INT, CONSTRAINT PK_tab1 PRIMARY KEY(col1))
> CREATE TABLE tab2 (
> col1 INT,
> col2 INT,
> CONSTRAINT PK_tab2 PRIMARY KEY(col1, col2),
> CONSTRAINT FK_tab2_tab1 FOREIGN KEY (col1) REFERENCES tab1(col1)
> )
> SELECT so1.[name], so1.id, so2.name, so2.id, so2.xtype, so2.type, *
> FROM sysobjects so1
> INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
> WHERE so1.[name] IN ('tab1', 'tab2')
> SELECT sc.*, 'XXX' xxx, so.* FROM sysconstraints sc INNER JOIN sysobjects
> so ON so.id = sc.id
> DECLARE @.HasFK SYSNAME
> SELECT @.HasFK = name
> from sysobjects
> where id in (
> SELECT fkeyid
> FROM sysreferences sr
> WHERE constid IN (
> SELECT so2.id
> FROM sysobjects so1
> INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
> )
> )
> DECLARE @.References SYSNAME
> SELECT @.References = name
> from sysobjects
> where id in (
> SELECT rkeyid
> FROM sysreferences sr
> WHERE constid IN (
> SELECT so2.id
> FROM sysobjects so1
> INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
> )
> )
> SELECT @.HasFK + ' has FK to ' + @.References
>
> "Sathiamoorthy" <someone@.microsoft.com> wrote in message
> news:uhuElgT8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I hava a table, called Items which has 2 foriegn keys. i want to find the
>> forign keys's primary key table through sql query.
>> Please anybody help me.
>> Regards,
>> R.Sathiamoorthy
>>
>

Find tables w/o primary keys but w/ unique clustered indexes

Hi
I run SQL Server 2000 SP3 on Windows 2000 SP4.
I want to find out, which "user tables" in my DB don't have
a primary key but do have at least one unique index. I
couldn't find any documented way to do this straight away.
So I came up with the following query, which seems to work:
SELECT OBJECT_NAME(id)
FROM sysindexes
WHERE indid > 0 and indid < 255 and (status & 2048) = 0
and (status & 2) <> 0
Does this query seem to be correct and is there any better
(documented) way of doing what I'm trying to do?
--
Thanks,
OskHow about:
SELECT DISTINCT
table_name,
constraint_name
FROM information_schema.table_constraints c
WHERE constraint_type = 'UNIQUE'
AND NOT EXISTS
(SELECT *
FROM information_schema.table_constraints c1
WHERE C1.table_name = c.table_name
and c1.constraint_type = 'PRIMARY KEY')
AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
'IsClustered') = 1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Try,
select distinct
object_name(a.[id])
from
sysindexes as a
left join
(
select
table_name
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
constraint_type = 'PRIMARY KEY'
) as b
on object_name(a.[id]) = b.table_name
where
b.table_name is null
and indexproperty(a.[id], [name], 'IsUnique') = 1
and objectproperty(a.[id], 'IsMSShipped') = 0
go
AMB
"Osk" wrote:
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Thanks!
Shame on me - IDEXPROPERTY was the way to go.
>--Original Message--
>Try,
>select distinct
> object_name(a.[id])
>from
> sysindexes as a
> left join
> (
> select
> table_name
> from
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where
> constraint_type = 'PRIMARY KEY'
> ) as b
> on object_name(a.[id]) = b.table_name
>where
> b.table_name is null
> and indexproperty(a.[id], [name], 'IsUnique') = 1
> and objectproperty(a.[id], 'IsMSShipped') = 0
>go
>
>AMB
>"Osk" wrote:
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>>
>.
>|||Thanks, Adam.
Still, I wasn't exactly looking for tables with UNIQUE
constraints but rather - with UNIQUE indexes.
>--Original Message--
>How about:
>SELECT DISTINCT
> table_name,
> constraint_name
>FROM information_schema.table_constraints c
>WHERE constraint_type = 'UNIQUE'
> AND NOT EXISTS
> (SELECT *
> FROM information_schema.table_constraints c1
> WHERE C1.table_name = c.table_name
> and c1.constraint_type = 'PRIMARY KEY')
> AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
>'IsClustered') = 1
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.datamanipulation.net
>--
>
>"Osk" <anonymous@.discussions.microsoft.com> wrote in message
>news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>
>.
>|||Lookup the meta data function OBJECTPROPERTY and its various possible values
for the second argument in SQL Server Books Online. You can get the list
using:
SELECT name
FROM sysobjects
WHERE s1.xtype = 'U'
AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
Alternatively, you can also use INFORMATION_SCHEMA views instead of
accessing the system table directly.
--
Anith|||Yes, Anith, thanks!
Though initially I wasn't really interested in finding
tables with UNIQUE constraints but rather with UNIQUE
indexes, which is not the same, is it?
>--Original Message--
>Lookup the meta data function OBJECTPROPERTY and its
various possible values
>for the second argument in SQL Server Books Online. You
can get the list
>using:
>SELECT name
> FROM sysobjects
> WHERE s1.xtype = 'U'
> AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
> AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
>Alternatively, you can also use INFORMATION_SCHEMA views
instead of
>accessing the system table directly.
>--
>Anith
>
>.
>

find primary and foreign keys

I have a DB with 100 tables. I was wondering if anybody knows a quick way to list primary and foreign key with the column name for all the tables.

Your help would make my life a lot easier

thanks

Hi,

The below will list all PK's:

SELECT o.name AS 'TableName',
c.name AS 'PKColumnName'
FROM sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk
WHERE o.type in ('U')
AND x.id = o.id
AND o.id = c.id
AND o.id = xk.id
AND x.indid = xk.indid
AND c.colid = xk.colid
AND xk.keyno <= x.keycnt
AND (x.status&32) = 0
AND convert(bit,(x.status & 0x800)/0x800) = 1
ORDER BY o.name, c.name

Cheers

Rob

|||Please use the INFORMATION_SCHEMA views instead of accesing system tables or using undocumented columns. The view INFORMATION_SCHEMA.KEY_COLUMN_USAGE will give the information you are looking for.