SQL 2005 SMO - find referencing table

Question

I need to change some primary keys from non-clustered to clustered but I can't drop the constraint because it is referenced from other foreign keys.

How can I find the tables that reference a primary key in the parent table as part of a foreign relation without looping through all tables in the DB? I need to disable the constraints on those, change the PK and re-enable.

Update:

1. I do not want to use plain SQL to do this but SMO only.

2. Marc, I know about ForeignKeys by I need something like: table.PrimaryKey.ForeignKeys (i.e. which tables are referencing my table's primary key) I just want to avoid looping through all the tables in the database and check the ForeignKeys property on each and every one of them to see if any of them reference my table.(not scalable)

Answer 1

Ok I think I found it.

table.Columns[0].EnumForeignKeys()

or directly

table.EnumForeignKeys()

I was expecting a property instead of a function. I am pretty sure behind the scenes it does what cmsjr suggested.

Answer 2

Using SMO, you could do this:

using Microsoft.SqlServer.Management.Smo;

Server localServer = new Server("your server name");
Database dasecoDB = localServer.Databases["your database name"];

Table table = dasecoDB.Tables["your table name"];
foreach(ForeignKey fk in table.ForeignKeys)
{
  Console.WriteLine("Foreign key {0} references table {1} and key {2}", fk.Name, fk.ReferencedTable, fk.ReferencedKey);
}

Marc