Web Software Architecture and Engineering – Life on the Bleeding Edge

I used to be a fan of MS SQL Server 2000. It was so easy to get up and running.
But, SQL Server 2005, is another story. To do even the most basic of things, requires, extensive knowledge or ugly workarounds.
We do a lot of merge replication with SQL Server 2005. We added some tables to two-way merges, and the replication failed. as usual, it was due to foreign key dependencies. (By default, SQL Sevrer wants to drop the table, and re-create it with replication properties.)
There is no command that I know of, where you can select the PK, and say, give me all foreign keys that link to this PK. Easy enough Use Case though, right? There are some scripts out there to give similar column names, but those aren’t accurate, because they dont have a foreign key reference necessarily.
After some searching, I found this. It was everything I needed. If you guys have better scripts, do share!

select cast(f.name  as varchar(255)) as foreign_key_name
    , r.keycnt
    , cast(c.name as  varchar(255)) as foreign_table
    , cast(fc.name as varchar(255)) as  foreign_column_1
    , cast(fc2.name as varchar(255)) as foreign_column_2
    ,  cast(p.name as varchar(255)) as primary_table
    , cast(rc.name as varchar(255))  as primary_column_1
    , cast(rc2.name as varchar(255)) as  primary_column_2
    from sysobjects f
    inner join sysobjects c on  f.parent_obj = c.id
    inner join sysreferences r on f.id =  r.constid
    inner join sysobjects p on r.rkeyid = p.id
    inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
    inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
    left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
    left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
    where f.type =  'F'
 ORDER BY cast(p.name as varchar(255))
 
About these ads

Comments on: "SQL Server Script to Find Foreign Key Dependencies" (16)

  1. Well the standard for the JDBC sql.MetaData interface specifies two functions, getImportedKeys() and getExportedKeys() to return foreign key information for a given table. Of course imported keys are where the primary key is in the foreign table, but exported keys are like you described, where the primary key is in the table you’re looking at. That being the case, and knowing that I’ve got the information from a java MetaData object against SQL 2005, I would think that there *has to* be something in SQL 2005 to provide that information. What or where I don’t know. So that doesn’t help you much if you need it within a SQL script.

    SQL Server does however support the information_schema standard and I would think you’d find that same information in the view information_schema.referential_constraints.

    hth

  2. Here’s a script I wrote a while back that might be a little more to the point.

    – Not rocket science here, but it can save some digging when looking for
    – a table that mysteriously references a table you’re trying to drop.
    declare @tableName varchar(200)
    set @tableName = ‘nativebatchoutputheader’
    select * from sys.foreign_keys k
    inner join sys.tables t on t.[object_id] = k.referenced_object_id
    where t.[name] = @tableName

  3. Fantastic!, just what i needed.

    The Caribou approach is simple if you are querying it on sql2005 but if you need to do maintenance on sql2000 you can not access sys.tables.

    Thank you again.

  4. Thanks, Carbou. It’s now a sproc in my dbs.

  5. Rune Brattas said:

    Hi,

    I found this code; which look similar to yours…
    …I am looking for the code to find all FK in my table and it’s reference Table.

    SELECT f.name AS ForeignKey,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,
    fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id,
    fc.referenced_column_id) AS ReferenceColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc
    ON f.OBJECT_ID = fc.constraint_object_id

    http://blog.sqlauthority.com/2007/09/04/sql-server-2005-find-tables-with-foreign-key-constraint-in-database/

    Thank You,
    Rune

  6. Thank You
    All

    It worked fine for me

  7. If you are using MS SQL SERVER MANAGEMENT STUDIO EXPRESS, then its very easy to find out the dependencies.

    just right click on the table name and click on viewdependencies. it will give u the list.

    Thanks.

  8. Adi,

    I found that the View Dependencies options actually gives incorrect or incomplete results on numerous occasions!

  9. alert(‘xxs’);

  10. Arun Raj R A said:

    Thanks.

    But how to find if we need to find a particular value is referred in the referenced tables?

  11. here is a script i have written which gives you keyOriginFrom, KeyInheritedBy, constraintName respectively

    select ‘['+ss.name+'].['+aO.name+']‘ keyOriginFrom, ‘['+s.name +'].['+t.name+']‘ KeyInheritedBy, fk.name constraintName
    from sys.tables t
    inner join sys.schemas s on t.schema_id = s.schema_id
    inner join sys.foreign_key_columns fkC on t.object_id = fkC.parent_object_id
    inner join sys.all_objects aO on fkC.referenced_object_id = aO.object_id
    inner join sys.foreign_keys fk on fkC.constraint_object_id =fk.object_id
    inner join sys.schemas ss on aO.schema_id = ss.schema_id
    where t.name=’testTime’

    it give you a result similar to this
    (each column value is eperated by commas)
    [General].[PaymentMode], [dbo].[testTime], FK_testTime_PaymentMode

    these values correspond keyOriginFrom, KeyInheritedBy, constraintName columns respectively

  12. Siegfried Niedinger said:

    Thank you for the script.

    I fixed a small problem with the script.
    [..]
    left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc2.colid
    left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc2.colid
    [..]

    (missing 2′s for the second column joins.)

  13. Thanks!

  14. nice work!
    it saves my day.

  15. This website truly has all the information and facts I wanted concerning this subject and didn’t know who to ask.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 414 other followers

%d bloggers like this: