How do i know where this is referred

7 Jul 2005 3:03 PM

thomson

Hi all,
i do have a column called Item_Code, which iam refering in so
many tables, i have set the foreign key relationship also.

My issue,
if i pass one Item_Code, i need to get a count how many
times this Item_Code is referred in other tables.

Thanks in advance

thomson

7 Jul 2005 3:47 PM

Thomas Coleman

Some DDL would be helpful. Using the Force we might have:

Create Table ItemCodes
(
    ItemCode VarChar(10) Not Null Primary Key
)

Create Table Referenced
(
    ItemCode VarChar(10) References ItemCodes(Item_Code)
    , ....
)

Select IC.ItemCodes, Count(*)
From ItemCodes As IC
    Left Join Referenced As R
        On IC.Item_Code = R.Item_Code

Thomas

Show quote

"thomson" <saintthom***@yahoo.com> wrote in message
news:1120748636.846410.120330@g43g2000cwa.googlegroups.com...
> Hi all,
> i do have a column called Item_Code, which iam refering in so
> many tables, i have set the foreign key relationship also.
>
> My issue,
> if i pass one Item_Code, i need to get a count how many
> times this Item_Code is referred in other tables.
>
> Thanks in advance
>
> thomson
>

8 Jul 2005 4:18 AM

thomson

The issue out here is there are almost 15 tables referring to this
particular primary key ,is there any system stored procedure or
function which will help us to know in this scenario

8 Jul 2005 4:24 PM

Thomas Coleman

At only 15 tables, I would write a stored proc that queries each of the 15
tables and accumulates a count.

Thomas

Show quote

"thomson" <saintthom***@yahoo.com> wrote in message
news:1120796282.749769.302400@g43g2000cwa.googlegroups.com...
> The issue out here is there are almost 15 tables referring to this
> particular primary key ,is there any system stored procedure or
> function which will help us to know in this scenario
>