finding foreign key info

Hi,
Is there a way to find out if a column on a table is a foreign key, what
table it references and what column on that table it is referencing. All of
my foreign keys are a single column, which should make things easier.
Any help would be greatly appreciated.
Regards,
NedYou can get this from a couple of INFORMATION_SCHEMA views, e.g.
select
pk_ccu.table_name as PK_Table,
pk_ccu.column_name as PK_Column,
fk_ccu.table_name as FK_Table,
fk_ccu.column_name as FK_Column
from
information_schema.constraint_column_usage pk_ccu
join information_schema.referential_constraints rc on
pk_ccu.constraint_name=rc.unique_constraint_name
join information_schema.constraint_column_usage fk_ccu on
rc.constraint_name=fk_ccu.constraint_name
where
fk_ccu.table_Name='table_in_question'
and fk_ccu.column_name='column_in_question'
Ned wrote:
> Hi,
> Is there a way to find out if a column on a table is a foreign key, what
> table it references and what column on that table it is referencing. All
of
> my foreign keys are a single column, which should make things easier.
> Any help would be greatly appreciated.
> Regards,
> Ned
>|||Thanks Trey,
Worked like a charm.
Regards,
Ned
"Trey Walpole" <treyNOpole@.comSPAMcast.net> wrote in message
news:exXjWn0rFHA.460@.TK2MSFTNGP15.phx.gbl...
> You can get this from a couple of INFORMATION_SCHEMA views, e.g.
> select
> pk_ccu.table_name as PK_Table,
> pk_ccu.column_name as PK_Column,
> fk_ccu.table_name as FK_Table,
> fk_ccu.column_name as FK_Column
> from
> information_schema.constraint_column_usage pk_ccu
> join information_schema.referential_constraints rc on
> pk_ccu.constraint_name=rc.unique_constraint_name
> join information_schema.constraint_column_usage fk_ccu on
> rc.constraint_name=fk_ccu.constraint_name
> where
> fk_ccu.table_Name='table_in_question'
> and fk_ccu.column_name='column_in_question'
>
> Ned wrote:

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

FIND THE FOREIGN KEY FOR A TABLE

Dear All,
I want to know, how to retrieve the foreign key for a table using T-SQL.
Regards,
SathiamoorthyHi
Look at Aaron's example
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME='Customers'
ORDER BY
1,2,3,4
"Sathiamoorthy" <someone@.microsoft.com> wrote in message
news:uIzkS2wKGHA.648@.TK2MSFTNGP14.phx.gbl...
> Dear All,
> I want to know, how to retrieve the foreign key for a table using T-SQL.
> Regards,
> Sathiamoorthy
>

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.

Find differences in Two tables

I have a parent and a child table. When I was loading the
child table I ran into foreign key error. I need to find
the different values in the text file I am loading into
the child table (Parent table is already loaded). There
are 4 columns in the foreign key definition. I have loaded
the text data into another table(did not include the
foreign key but the indexes).
I have the following query that is taking forever in a
table with 88000 rows. Is there a quicker query (Both
tables have the necessary indexes)'.
Select * from another an
Join parent pr on
an.col1 <> pr.col1 AND an.col2 <> pr.col2
an.col3 <> pr.col3 AND an.col4 <> pr.col4
Thanks for any helpTry:
Select * from another an
left outer Join parent pr on
an.col1 = pr.col1 AND an.col2 = pr.col2
an.col3 = pr.col3 AND an.col4 = pr.col4
WHERE pr.col1 IS NULL
Unequality (<> ) is not sargeable, i.e. the Query optimizer can not use
indexes when trying to solve an unequality, but it can use indexes when
trying to solve equality operations.
Jacco Schalkwijk
SQL Server MVP
"Kavin" <anonymous@.discussions.microsoft.com> wrote in message
news:93e401c40522$eb548bb0$a101280a@.phx.gbl...
> I have a parent and a child table. When I was loading the
> child table I ran into foreign key error. I need to find
> the different values in the text file I am loading into
> the child table (Parent table is already loaded). There
> are 4 columns in the foreign key definition. I have loaded
> the text data into another table(did not include the
> foreign key but the indexes).
> I have the following query that is taking forever in a
> table with 88000 rows. Is there a quicker query (Both
> tables have the necessary indexes)'.
> Select * from another an
> Join parent pr on
> an.col1 <> pr.col1 AND an.col2 <> pr.col2
> an.col3 <> pr.col3 AND an.col4 <> pr.col4
> Thanks for any help
>|||That did it.......
Thanks.

>--Original Message--
>Try:
>Select * from another an
>left outer Join parent pr on
>an.col1 = pr.col1 AND an.col2 = pr.col2
>an.col3 = pr.col3 AND an.col4 = pr.col4
>WHERE pr.col1 IS NULL
>Unequality (<> ) is not sargeable, i.e. the Query
optimizer can not use
>indexes when trying to solve an unequality, but it can
use indexes when
>trying to solve equality operations.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Kavin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:93e401c40522$eb548bb0$a101280a@.phx.gbl...
the
loaded
>
>.
>