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))
Comments on: "SQL Server Script to Find Foreign Key Dependencies" (15)
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
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
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.
Thanks, Carbou. It’s now a sproc in my dbs.
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
Thank You
All
It worked fine for me
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.
Adi,
I found that the View Dependencies options actually gives incorrect or incomplete results on numerous occasions!
alert(‘xxs’);
Thanks.
But how to find if we need to find a particular value is referred in the referenced tables?
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
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.)
Siegfried – seems to work for me as is. Hmm.
Thanks!
nice work!
it saves my day.