How to find the Froiengn key refrence column and refrence table through T-SQL

Discussion in 'microsoft.public.sqlserver.programming' started by Sathiamoorthy, Feb 8, 2006.

  1. Hi,

    I am creating a tool for generating SQL Scripts.
    I want the sql statement for getting the table name , column name ,
    reference table name ,reference column name of a particular foreign key.
    In this case I know only the foreign key name.

    Thanks and Regards,

    Sathiamoorthy
     
    Sathiamoorthy, Feb 8, 2006
    #1
    1. Advertisements

  2. Sathiamoorthy

    Uri Dimant Guest

    OJ has written this script

    create procedure usp_findreferences
    @tbname sysname=null
    as
    set nocount on


    Print 'Referenced:'
    select c1.table_name,
    c1.column_name,
    fkey=r.constraint_name,
    referenced_parent_table=c2.table_name,
    c2.column_name
    from information_schema.constraint_column_usage c1 join
    information_schema.referential_constraints r on
    c1.constraint_name=r.constraint_name
    join information_schema.constraint_column_usage c2 on
    r.unique_constraint_name=c2.constraint_name
    where c1.table_name=coalesce(@tbname,c1.table_name)
    order by case when @tbname is null then c1.table_name else c2.table_name end


    print ''
    print 'Referencing:'
    select c1.table_name,
    c1.column_name,
    fkey=r.constraint_name,
    referencing_child_table=c2.table_name,
    c2.column_name
    from information_schema.constraint_column_usage c1 join
    information_schema.referential_constraints r on
    c1.constraint_name=r.unique_constraint_name
    join information_schema.constraint_column_usage c2 on
    r.constraint_name=c2.constraint_name
    where c1.table_name=coalesce(@tbname,c1.table_name)
    order by case when @tbname is null then c1.table_name else c2.table_name end
    go


    --test run
    exec usp_findreferences 'Orders'
    drop proc usp_findreferences
     
    Uri Dimant, Feb 8, 2006
    #2
    1. Advertisements

  3. Thank you very much.

    sathyamoorthy

     
    Sathiamoorthy, Feb 8, 2006
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.