Foreign Key identifying

Question

Post reply

Foreign Key identifying

gk-411903

Hall of Fame

Points: 3801
More actions
April 24, 2009 at 12:00 pm

#202419

Hi
I ran the following query to see the list of child tables
SELECT c1.column_name AS COLUMN_NAME,
c1.table_name AS PK_TABLE_NAME,
c2.table_name AS FK_TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c1 INNER JOIN
INFORMATION_SCHEMA.COLUMNS c2
ON c1.COLUMN_NAME = c2.COLUMN_NAME
AND c1.DATA_TYPE = c2.DATA_TYPE
AND c1.TABLE_NAME <> c2.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON c1.COLUMN_NAME = ccu.COLUMN_NAME
AND c1.TABLE_NAME = ccu.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON t.TABLE_NAME = c2.TABLE_NAME
WHERE tc.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') -- Anything a foreign -- key can reference
AND t.TABLE_TYPE = 'BASE TABLE' -- Eliminate views.
AND t.TABLE_NAME NOT LIKE 'sys%' -- Eliminate system tables.
AND t.table_name = 'FILTER'
It came up with
COLUMN_NAMEPK_TABLE_NAMEFK_TABLE_NAME
nameactlink_macro_parmfilter
filterIdfilter_callfilter
filterIdfilter_exitfilter
filterIdfilter_gotofilter
filterIdfilter_gotoactionfilter
filterIdfilter_logfilter
filterIdfilter_mappingfilter
filterIdfilter_messagefilter
filterIdfilter_notifyfilter
filterIdfilter_notify_idsfilter
filterIdfilter_processfilter
filterIdfilter_pushfilter
filterIdfilter_setfilter
filterIdfilter_sqlfilter
indicating filter_call is one of the child tables.
But when i do 'script to' in Mgmt.Studio the script does not show up any foreign references for this child table
USE [ARUAT]
GO
/****** Object: Table [dbo].[filter_call] Script Date: 04/24/2009 10:57:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[filter_call](
[filterId] [int] NOT NULL,
[actionIndex] [int] NOT NULL,
[serverName] [varchar](64) NOT NULL,
[guideName] [varchar](254) NOT NULL,
[guideMode] [int] NOT NULL,
[guideTableId] [int] NULL,
[assignShort] [varchar](255) NULL,
[assignLong] [text] NULL,
[sampleServer] [varchar](64) NULL,
[sampleGuide] [varchar](254) NULL,
PRIMARY KEY CLUSTERED
(
[filterId] ASC,
[actionIndex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
When i click on constraints for this child table i am not seeing one.
There is one primary index and when i script to i get the following
USE [ARUAT]
GO
/****** Object: Index [PK__filter_call__66603565] Script Date: 04/24/2009 10:59:09 ******/
ALTER TABLE [dbo].[filter_call] ADD PRIMARY KEY CLUSTERED
(
[filterId] ASC,
[actionIndex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF
, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Can someone identify what's wrong with the first script?
TIA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 1

You can use

select * from sysconstraints

sp_fkeys 'Tbl_name'

sp_Pkeys 'Tbl_name'

gk-411903 Hall of Fame Points: 3801 More actions · Answer 2

I tried the following query to identify the types of constraints i am having

SELECT c.status,count(*) FROM sysconstraints c

GROUP BY c.status

ORDER BY 1;

and it came up with

1331416

1336652529

1341776

But as per the following URL http://msdn.microsoft.com/en-us/library/ms190365.aspx the values should be in single digit.

How do i get the value for FKey constraints in status?

TIa

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

Better use catalog views for this.

Check-->http://msdn.microsoft.com/en-us/library/ms190365.aspx

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead.

MJ