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))
Nov 1, 2008 at 5:47 PM 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
Dec 17, 2008 at 6:50 AM 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