Finding columns that are intended for foreign keys

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding columns that are intended for foreign keys

Author

Topic

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-24 : 06:53:22

Hi Friends,

me again. I am trying to find out if all of my intended foreign keys are actually set as foreign keys programmatically.

Ive stuffed something up, please find my error

USE RIQDB1
SELECT DISTINCT 'Alter table '+ table_name +
' ADD CONSTRAINT DF_'+ table_name + '_' + column_name +
' DEFAULT ' + ''''' FOR '+ column_name FROM Information_schema.columns
WHERE ((column_name Like '%fk%') AND (FROM Information_schema.type = F))
go

thanks
Cm

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-24 : 07:45:20

What are you trying to do ???

and what is Information_Schema.Type???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-24 : 07:54:21

please show me an example of how to select columns that are foreign keys

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-24 : 08:14:46

Are you looking for somthing like this

Select SysObjects.[Name] As [Contraint Name] ,Tab.[Name] as [Table Name],Col.[Name] As [Column Name]
From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab
On Tab.[ID] = Sysobjects.[Parent_Obj]
Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID]
Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
Where Sysobjects.Xtype = 'F'

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-24 : 08:19:32

How on earth did you do that????
Dear Chiragkhabaria,

Boy have I got a lot to learn.

Thankyou so much

Cm

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-24 : 08:27:43

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-24 : 10:43:56

Other mthod

select Constraint_name,table_name, column_name from Information_schema.key_column_usage
where objectProperty(object_id(Constraint_name),'IsForeignKey')=1

Madhivanan

Failing to plan is Planning to fail

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-24 : 22:36:48

Dear Friends

Well on a closer look every method used, brings up foreign keys, not columns intended for foreign key relationships. I am trying to find the ones I have missed.

The column names all start with 'fk_%'

Looking in the information_schema.key_column_usage will bring up those registered as foreign keys, I want those in the information_schema.columns that have fk_% but are NOT registered as foreign keys.

Thankyou
Cm

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-25 : 01:29:06

Do you mean this?

select Constraint_name,table_name, column_name from Information_schema.key_column_usage
where column_name like 'fk_%'

Madhivanan

Failing to plan is Planning to fail

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-04-25 : 02:58:19

With apologies for the shouting, this will get you the columns with names starting "fk_" that are not involved in any foreign key relationships:


SELECT C.*
FROM INFORMATION_SCHEMA.COLUMNS AS C
INNER JOIN INFORMATION_SCHEMA.TABLES AS T
  ON C.TABLE_CATALOG = T.TABLE_CATALOG
 AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
 AND C.TABLE_NAME = T.TABLE_NAME
LEFT JOIN (
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
      ON CCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
     AND CCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
     AND CCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
  )
  ON C.TABLE_CATALOG = CCU.TABLE_CATALOG
 AND C.TABLE_SCHEMA = CCU.TABLE_SCHEMA
 AND C.TABLE_NAME = CCU.TABLE_NAME
 AND C.COLUMN_NAME = CCU.COLUMN_NAME
WHERE C.COLUMN_NAME LIKE 'fk[_]%'
  AND CCU.COLUMN_NAME IS NULL
  AND T.TABLE_TYPE = 'BASE TABLE'
ORDER BY C.TABLE_CATALOG, C.TABLE_NAME, C.TABLE_SCHEMA, C.ORDINAL_POSITION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources