December 11, 2006 at 3:37 pm
Guys,
I am trying to join sysforeignkeys, sysobjects and information_schema.columns to get the details of the foreignkeys. But somehow I dont seem to get the desired output.
Below query is how far I got
SELECT T.NAME FNAME, O.NAME PNAME, F.FKEY, S.NAME CNAME, F.RKEY
FROM SYSFOREIGNKEYS F INNER JOIN SYSOBJECTS O ON F.FKEYID = O.ID
INNER JOIN SYSOBJECTS S ON F.RKEYID = S.ID
INNER JOIN SYSOBJECTS T ON F.CONSTID = T.ID
fname - foreignkey name
pname - parent table name
pcol - parent column name
pdtype - parent column datatype
cname - child table name
ccol - child column name
cdtype - child column datatype
My desired output is something like below.
cname pname pcol pdtype cname col cdtype
Any suggestions/inputs would help
Thanks
December 11, 2006 at 4:32 pm
You may get better info from the following...
select * from sys.foreign_keys
MohammedU
Microsoft SQL Server MVP
December 11, 2006 at 5:31 pm
I'm guessing you are using SQL Server 2000 (although this is a 2005 forum). If that is the case, something like this:
SELECT OBJECT_NAME(sfk.constid) FNAME, OBJECT_NAME(sfk.fkeyid) PNAME, sc1.name PCOL, st1.name PDTYPE, OBJECT_NAME(sfk.rkeyid) CNAME, sc2.name CCOL, st2.name CDTYPE FROM sysforeignkeys sfk JOIN syscolumns sc1 ON sfk.fkeyid = sc1.id AND sfk.fkey = sc1.colid JOIN systypes st1 ON sc1.xtype = st1.xtype JOIN syscolumns sc2 ON sfk.rkeyid = sc2.id AND sfk.rkey = sc2.colid JOIN systypes st2 ON sc2.xtype = st2.xtype WHERE st1.name <> 'sysname' AND st2.name <> 'sysname' ORDER BY FNAME, PNAME, CNAME, keyno
K. Brian Kelley
@kbriankelley
December 12, 2006 at 4:40 am
Or you could make it really simple.
Select
* from information_Schema.constraint_column_usage
Should work on both SQL 2000 and SQL 2005 and gives you table names as well as column names.
December 12, 2006 at 7:33 am
True, but it doesn't give him the datatype he's looking for.
K. Brian Kelley
@kbriankelley
December 12, 2006 at 8:23 am
Oh, right.
Try joining to Information_Schema.Columns on TableName and ColumnName. This should give you all the column information details.
How are you guys getting your code pasted into one line separation? Every time I try, it adds a double-space between each line of my code. It's driving me nuts.
Thanks,
December 12, 2006 at 11:03 am
Are you copying and pasting code?
K. Brian Kelley
@kbriankelley
December 12, 2006 at 12:07 pm
I'm trying to. And it always double spaces it even though SSMS has things single spaced.
December 12, 2006 at 12:27 pm
I always copy and paste into notepad first.
K. Brian Kelley
@kbriankelley
December 13, 2006 at 4:30 am
Ah. Thanks, Brian. I'll try that.
Select cu.*, c.Data_Type, c.Numeric_Precision, c.Column_Default
from information_schema.Constraint_Column_Usage cu
join information_Schema.Columns c
on cu.Table_Name = c.Table_Name
and cu.Column_Name = c.Column_Name
WHOOHOO! It works. Thanks again. And, this is the query I'd recommend to find all you need. You'll want to tweak the SELECT list, but it should work for you.
December 13, 2006 at 7:42 am
You can press Ctrl + Enter to get a normally spaced carriage return.
Brandie's script returns all constraints, not just foreign keys. And if the constraint spans more than one column, you'll get one row for each. You can group by table name and column name but then you'll lose the data type.
John
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply