Jan,
It's ages and ages old, by copy this and paste it into your editor, save it,
and run it.
No guarantees, because it is sooooo old.
Bill
*(/------------------------------------------------------------------------\
Program Name: FindFKs.rmd -- Find Foreign Keys
Copyright (c) 1996, Bill Downall, All Rights Reserved.
Purpose: Find all foreign key constraints that reference
a particular primary key.
Is Run by: user at R> prompt: RUN findfks.rmd
MODIFICATION HISTORY
Created: 5/96
\------------------------------------------------------------------------/)
SET QUOTE ='
-- all variables start with vffk so that clearing them
-- is unlikely to clear YOUR variables.
SET VAR vffkDBName = (CVAL('database')) -- make sure user is connected
IF vffkDBName IS NULL THEN
PAUSE 2 USING 'You must be connected to a database. Press any key.'
GOTO erlyexit
ENDIF
CLS
WRITE 'FindFKs.rmd -- Find Foreign Keys '
WRITE
'---------------------------------------------------------------------'
WRITE ' Copyright (c) 1996, Bill Downall'
WRITE ' '
SET VAR vffkPKTabID INTEGER
SET VAR vffkChooseVar TEXT
CHOOSE vffkChooseVar +
FROM #VALUES FOR +
t1.sys_table_name, t1.sys_table_id +
FROM sys_tables t1 +
WHERE (t1.sys_table_name NOT LIKE 'sys%') +
AND (t1.sys_table_id IN +
(SELECT sys_table_id FROM sys_indexes i2 +
WHERE (i2.sys_primary_key = 1) +
AND (i2.sys_table_id = t1.sys_table_id))) +
ORDER BY sys_table_name +
AT 5, CENTER +
TITLE 'Choose a Primary Key table'
IF vffkChooseVar = '[Esc]' THEN
GOTO Erlyexit
ENDIF
SET VAR vffkpktabid = (INT(.vffkchoosevar))
SELECT sys_table_name INTO vffkPKTabName +
FROM sys_tables WHERE (sys_table_id = .vffkPKTabID)
CLS
WRITE 'FK''s that reference the PK in table', .vffkPKTabName, ':'
WRITE ' '
SET HEADINGS OFF
WRITE ' FK ID FK Name FK Table FK Column'
WRITE ' ----- ------------------ ------------------ ------------------'
SELECT fk2.sys_index_id=5, +
fk2.sys_index_name=18, +
ft3.sys_table_name=18, +
fk2.sys_column_name=18 +
FROM sys_indexes pk1, sys_indexes fk2, sys_tables ft3 +
WHERE (pk1.sys_table_id = .vffkPKTabID) +
AND (pk1.sys_primary_key = 1) +
AND (fk2.sys_foreign_key = pk1.sys_index_id) +
AND (fk2.sys_table_id = ft3.sys_table_id) +
ORDER BY fk2.sys_index_id, fk2.sys_column_name
if sqlcode = 100 then
WRITE 'The Primary Key in this table is NOT referenced by any Foreign
Keys.'
ENDIF
SET HEADINGS ON -- Clean up
LABEL ErlyExit
CLEAR VAR vffk%
RETURN
On Fri, Oct 3, 2008 at 6:47 PM, jan johansen <[EMAIL PROTECTED]>wrote:
> Group,
>
> Having a challenge. Can't delete a table because it says it's referenced
> by a foreign key. I've looked all through my tables and can't find a
> reference
> to that table.
>
> Suggestions?
>
> Jan
>
