Dear All,
I am trying to write some SQL that will give a list of all tables and
Primary Key/ Foreign Key constraints in a database. The code below goes part
of the way but not what I would like. It gives me:
Parent Table: Activities
Child Table: ActivitiesLocation
ForeignKey: 1
PrimaryKey: 17
TotalKeys: 1
(where 1 in the column name in the Activities table that is the Foreign Key
and column name 17 is where the Primary Key is to be found.)
Script:
SELECT TOP 100 PERCENT so1.name AS 'Parent Table', so2.name AS 'Child
Table', sf.fkey AS ForeignKey, sf.rkey AS PrimaryKey, sf.keyno AS TotalKeys
FROM dbo.sysforeignkeys sf INNER JOIN
dbo.sysobjects so1 ON so1.id = sf.rkeyid INNER JOIN
dbo.sysobjects so2 ON so2.id = sf.fkeyid
ORDER BY so1.name
How can I get a list of the column name of column no 17 in the Activies
Table and column no 1 in the ActivitiesLocation table. Is there a better way
to get a listing of the PK/FK for a database along with table names?
Thanks again.
Alastair> I am trying to write some SQL that will give a list of all tables and
> Primary Key/ Foreign Key constraints in a database.
http://www.aspfaq.com/search.asp?q=schema%3A|||Check out the information_schema.key_column_usage view. It should get you
started with what you need.
--Brian
(Please reply to the newsgroups only.)
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:9197A5C9-182B-451B-AE40-05DA1F98E62B@.microsoft.com...
> Dear All,
> I am trying to write some SQL that will give a list of all tables and
> Primary Key/ Foreign Key constraints in a database. The code below goes
> part
> of the way but not what I would like. It gives me:
> Parent Table: Activities
> Child Table: ActivitiesLocation
> ForeignKey: 1
> PrimaryKey: 17
> TotalKeys: 1
> (where 1 in the column name in the Activities table that is the Foreign
> Key
> and column name 17 is where the Primary Key is to be found.)
> Script:
> SELECT TOP 100 PERCENT so1.name AS 'Parent Table', so2.name AS 'Child
> Table', sf.fkey AS ForeignKey, sf.rkey AS PrimaryKey, sf.keyno AS
> TotalKeys
> FROM dbo.sysforeignkeys sf INNER JOIN
> dbo.sysobjects so1 ON so1.id = sf.rkeyid INNER JOIN
> dbo.sysobjects so2 ON so2.id = sf.fkeyid
> ORDER BY so1.name
> How can I get a list of the column name of column no 17 in the Activies
> Table and column no 1 in the ActivitiesLocation table. Is there a better
> way
> to get a listing of the PK/FK for a database along with table names?
> Thanks again.
> Alastair
>|||Thanks Aaron and Brian for both your replies. This group is definately on th
e
ball. Exactly what I wanted.
Alastair
"Aaron Bertrand [SQL Server MVP]" wrote:
> http://www.aspfaq.com/search.asp?q=schema%3A
>
>
Showing posts with label constraints. Show all posts
Showing posts with label constraints. Show all posts
Monday, March 12, 2012
Friday, February 24, 2012
list constraints
how can i use query analyzer to list the constraints for a given table?hi
for this u can use sp_help <tablename>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_help_304w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"mcnewsxp" wrote:
> how can i use query analyzer to list the constraints for a given table?
>
>|||Hi,
You could also query the information schema system view.
Select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where table_name='tablename'
Thanks
Hari
SQL Server MVP
"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:utbw6EElFHA.2920@.TK2MSFTNGP14.phx.gbl...
> how can i use query analyzer to list the constraints for a given table?
>
for this u can use sp_help <tablename>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_help_304w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"mcnewsxp" wrote:
> how can i use query analyzer to list the constraints for a given table?
>
>|||Hi,
You could also query the information schema system view.
Select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where table_name='tablename'
Thanks
Hari
SQL Server MVP
"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:utbw6EElFHA.2920@.TK2MSFTNGP14.phx.gbl...
> how can i use query analyzer to list the constraints for a given table?
>
list constraints
how can i use query analyzer to list the constraints for a given table?
hi
for this u can use sp_help <tablename>
http://msdn.microsoft.com/library/de..._help_304w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"mcnewsxp" wrote:
> how can i use query analyzer to list the constraints for a given table?
>
>
|||Hi,
You could also query the information schema system view.
Select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where table_name='tablename'
Thanks
Hari
SQL Server MVP
"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:utbw6EElFHA.2920@.TK2MSFTNGP14.phx.gbl...
> how can i use query analyzer to list the constraints for a given table?
>
hi
for this u can use sp_help <tablename>
http://msdn.microsoft.com/library/de..._help_304w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"mcnewsxp" wrote:
> how can i use query analyzer to list the constraints for a given table?
>
>
|||Hi,
You could also query the information schema system view.
Select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where table_name='tablename'
Thanks
Hari
SQL Server MVP
"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:utbw6EElFHA.2920@.TK2MSFTNGP14.phx.gbl...
> how can i use query analyzer to list the constraints for a given table?
>
list constraints
how can i use query analyzer to list the constraints for a given table?hi
for this u can use sp_help <tablename>
http://msdn.microsoft.com/library/d... />
p_304w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"mcnewsxp" wrote:
> how can i use query analyzer to list the constraints for a given table?
>
>|||Hi,
You could also query the information schema system view.
Select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where table_name='tablename'
Thanks
Hari
SQL Server MVP
"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:utbw6EElFHA.2920@.TK2MSFTNGP14.phx.gbl...
> how can i use query analyzer to list the constraints for a given table?
>
for this u can use sp_help <tablename>
http://msdn.microsoft.com/library/d... />
p_304w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"mcnewsxp" wrote:
> how can i use query analyzer to list the constraints for a given table?
>
>|||Hi,
You could also query the information schema system view.
Select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where table_name='tablename'
Thanks
Hari
SQL Server MVP
"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:utbw6EElFHA.2920@.TK2MSFTNGP14.phx.gbl...
> how can i use query analyzer to list the constraints for a given table?
>
Subscribe to:
Posts (Atom)