Obtaining Primary and Foreign Key information from a table

Hi,
I would like to query the system tables in order to obtain all the Primary
and Foreign keys for any particular table.
I have tried using the syscolumns.colstat field but it only outputs zero. I
understand it should output 1 if the column is a primary key.
Also I do not know I do not know how to obtain Foreign key info on a table.
All help is much appreciated.
Kind regards,
Polly AnnaWhat version of SQL Server are you using?
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> Hi,
> I would like to query the system tables in order to obtain all the Primary
> and Foreign keys for any particular table.
> I have tried using the syscolumns.colstat field but it only outputs zero.
> I
> understand it should output 1 if the column is a primary key.
> Also I do not know I do not know how to obtain Foreign key info on a
> table.
> All help is much appreciated.
> Kind regards,
> Polly Anna|||Hi Aaron,
I am using my query against both a SQL2000 and SQL2005 db.
I attach the query below.
Many thanks for your help.
Polly Anna
select distinct column_name as Column_Name,
data_type,
c.length,
CASE c.colStat
WHEN 1 THEN 'Y' ELSE ''
END AS PrimaryKey
from information_schema.columns as isc
left join sysobjects o
on isc.table_name = o.name
left join syscolumns c
on o.id = c.id and c.name = isc.column_name
where table_name = 'Dentist'
"Aaron Bertrand [SQL Server MVP]" wrote:
> What version of SQL Server are you using?
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> > Hi,
> >
> > I would like to query the system tables in order to obtain all the Primary
> > and Foreign keys for any particular table.
> >
> > I have tried using the syscolumns.colstat field but it only outputs zero.
> > I
> > understand it should output 1 if the column is a primary key.
> >
> > Also I do not know I do not know how to obtain Foreign key info on a
> > table.
> >
> > All help is much appreciated.
> >
> > Kind regards,
> >
> > Polly Anna
>
>|||Polly
SS2005
select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
, k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
, c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
from sys.key_constraints as k
join sys.tables as t
on t.object_id = k.parent_object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.index_columns as ic
on ic.object_id = t.object_id
and ic.index_id = k.unique_index_id
join sys.columns as c
on c.object_id = t.object_id
and c.column_id = ic.column_id
order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
ORDINAL_POSITION;
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> Hi Aaron,
> I am using my query against both a SQL2000 and SQL2005 db.
> I attach the query below.
> Many thanks for your help.
> Polly Anna
> select distinct column_name as Column_Name,
> data_type,
> c.length,
> CASE c.colStat
> WHEN 1 THEN 'Y' ELSE ''
> END AS PrimaryKey
> from information_schema.columns as isc
> left join sysobjects o
> on isc.table_name = o.name
> left join syscolumns c
> on o.id = c.id and c.name = isc.column_name
> where table_name = 'Dentist'
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> What version of SQL Server are you using?
>>
>> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
>> > Hi,
>> >
>> > I would like to query the system tables in order to obtain all the
>> > Primary
>> > and Foreign keys for any particular table.
>> >
>> > I have tried using the syscolumns.colstat field but it only outputs
>> > zero.
>> > I
>> > understand it should output 1 if the column is a primary key.
>> >
>> > Also I do not know I do not know how to obtain Foreign key info on a
>> > table.
>> >
>> > All help is much appreciated.
>> >
>> > Kind regards,
>> >
>> > Polly Anna
>>|||Hi Uri,
yes thank you, that is brilliant. It works in SS2005. You don't happen to
have the script for SQL2000?
Thank you very much indeed.
Kind regards,
Polly Anna
"Uri Dimant" wrote:
> Polly
> SS2005
> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
> from sys.key_constraints as k
> join sys.tables as t
> on t.object_id = k.parent_object_id
> join sys.schemas as s
> on s.schema_id = t.schema_id
> join sys.index_columns as ic
> on ic.object_id = t.object_id
> and ic.index_id = k.unique_index_id
> join sys.columns as c
> on c.object_id = t.object_id
> and c.column_id = ic.column_id
> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
> ORDINAL_POSITION;
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> > Hi Aaron,
> >
> > I am using my query against both a SQL2000 and SQL2005 db.
> >
> > I attach the query below.
> >
> > Many thanks for your help.
> >
> > Polly Anna
> >
> > select distinct column_name as Column_Name,
> > data_type,
> > c.length,
> > CASE c.colStat
> > WHEN 1 THEN 'Y' ELSE ''
> > END AS PrimaryKey
> >
> > from information_schema.columns as isc
> > left join sysobjects o
> > on isc.table_name = o.name
> > left join syscolumns c
> > on o.id = c.id and c.name = isc.column_name
> > where table_name = 'Dentist'
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> What version of SQL Server are you using?
> >>
> >>
> >>
> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> >> > Hi,
> >> >
> >> > I would like to query the system tables in order to obtain all the
> >> > Primary
> >> > and Foreign keys for any particular table.
> >> >
> >> > I have tried using the syscolumns.colstat field but it only outputs
> >> > zero.
> >> > I
> >> > understand it should output 1 if the column is a primary key.
> >> >
> >> > Also I do not know I do not know how to obtain Foreign key info on a
> >> > table.
> >> >
> >> > All help is much appreciated.
> >> >
> >> > Kind regards,
> >> >
> >> > Polly Anna
> >>
> >>
> >>
>
>|||SELECT t.TABLE_SCHEMA,
t.TABLE_NAME, c.CONSTRAINT_NAME,
c.CONSTRAINT_TYPE, k.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
AND c.TABLE_NAME = k.TABLE_NAME
WHERE
c.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY 1,2,4 DESC,3;
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5271BE12-A6E9-4C72-B3FC-17AF8AF1B213@.microsoft.com...
> Hi Uri,
> yes thank you, that is brilliant. It works in SS2005. You don't happen to
> have the script for SQL2000?
> Thank you very much indeed.
> Kind regards,
> Polly Anna
> "Uri Dimant" wrote:
>> Polly
>> SS2005
>> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
>> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
>> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
>> from sys.key_constraints as k
>> join sys.tables as t
>> on t.object_id = k.parent_object_id
>> join sys.schemas as s
>> on s.schema_id = t.schema_id
>> join sys.index_columns as ic
>> on ic.object_id = t.object_id
>> and ic.index_id = k.unique_index_id
>> join sys.columns as c
>> on c.object_id = t.object_id
>> and c.column_id = ic.column_id
>> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
>> ORDINAL_POSITION;
>>
>> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
>> > Hi Aaron,
>> >
>> > I am using my query against both a SQL2000 and SQL2005 db.
>> >
>> > I attach the query below.
>> >
>> > Many thanks for your help.
>> >
>> > Polly Anna
>> >
>> > select distinct column_name as Column_Name,
>> > data_type,
>> > c.length,
>> > CASE c.colStat
>> > WHEN 1 THEN 'Y' ELSE ''
>> > END AS PrimaryKey
>> >
>> > from information_schema.columns as isc
>> > left join sysobjects o
>> > on isc.table_name = o.name
>> > left join syscolumns c
>> > on o.id = c.id and c.name = isc.column_name
>> > where table_name = 'Dentist'
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> >> What version of SQL Server are you using?
>> >>
>> >>
>> >>
>> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I would like to query the system tables in order to obtain all the
>> >> > Primary
>> >> > and Foreign keys for any particular table.
>> >> >
>> >> > I have tried using the syscolumns.colstat field but it only outputs
>> >> > zero.
>> >> > I
>> >> > understand it should output 1 if the column is a primary key.
>> >> >
>> >> > Also I do not know I do not know how to obtain Foreign key info on a
>> >> > table.
>> >> >
>> >> > All help is much appreciated.
>> >> >
>> >> > Kind regards,
>> >> >
>> >> > Polly Anna
>> >>
>> >>
>> >>
>>|||Hi Aaron,
it works like a charm. Thank you so much for your help.
Kind regards,
Polly Anna
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT t.TABLE_SCHEMA,
> t.TABLE_NAME, c.CONSTRAINT_NAME,
> c.CONSTRAINT_TYPE, k.COLUMN_NAME
> FROM INFORMATION_SCHEMA.TABLES t
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
> ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
> AND t.TABLE_NAME = c.TABLE_NAME
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
> ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
> AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
> AND c.TABLE_NAME = k.TABLE_NAME
> WHERE
> c.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
> ORDER BY 1,2,4 DESC,3;
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5271BE12-A6E9-4C72-B3FC-17AF8AF1B213@.microsoft.com...
> > Hi Uri,
> >
> > yes thank you, that is brilliant. It works in SS2005. You don't happen to
> > have the script for SQL2000?
> >
> > Thank you very much indeed.
> >
> > Kind regards,
> >
> > Polly Anna
> >
> > "Uri Dimant" wrote:
> >
> >> Polly
> >> SS2005
> >> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
> >> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
> >> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
> >> from sys.key_constraints as k
> >> join sys.tables as t
> >> on t.object_id = k.parent_object_id
> >> join sys.schemas as s
> >> on s.schema_id = t.schema_id
> >> join sys.index_columns as ic
> >> on ic.object_id = t.object_id
> >> and ic.index_id = k.unique_index_id
> >> join sys.columns as c
> >> on c.object_id = t.object_id
> >> and c.column_id = ic.column_id
> >> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
> >> ORDINAL_POSITION;
> >>
> >>
> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> >> > Hi Aaron,
> >> >
> >> > I am using my query against both a SQL2000 and SQL2005 db.
> >> >
> >> > I attach the query below.
> >> >
> >> > Many thanks for your help.
> >> >
> >> > Polly Anna
> >> >
> >> > select distinct column_name as Column_Name,
> >> > data_type,
> >> > c.length,
> >> > CASE c.colStat
> >> > WHEN 1 THEN 'Y' ELSE ''
> >> > END AS PrimaryKey
> >> >
> >> > from information_schema.columns as isc
> >> > left join sysobjects o
> >> > on isc.table_name = o.name
> >> > left join syscolumns c
> >> > on o.id = c.id and c.name = isc.column_name
> >> > where table_name = 'Dentist'
> >> >
> >> >
> >> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >> >
> >> >> What version of SQL Server are you using?
> >> >>
> >> >>
> >> >>
> >> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I would like to query the system tables in order to obtain all the
> >> >> > Primary
> >> >> > and Foreign keys for any particular table.
> >> >> >
> >> >> > I have tried using the syscolumns.colstat field but it only outputs
> >> >> > zero.
> >> >> > I
> >> >> > understand it should output 1 if the column is a primary key.
> >> >> >
> >> >> > Also I do not know I do not know how to obtain Foreign key info on a
> >> >> > table.
> >> >> >
> >> >> > All help is much appreciated.
> >> >> >
> >> >> > Kind regards,
> >> >> >
> >> >> > Polly Anna
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>