Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL 2005 SMO - find referencing table

Tags:

sql-server

smo

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)

like image 510
Cosmin Onea Avatar asked Apr 19 '09 14:04

Cosmin Onea


2 Answers

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.

like image 121
Cosmin Onea Avatar answered Nov 15 '22 05:11

Cosmin Onea


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

like image 40
marc_s Avatar answered Nov 15 '22 05:11

marc_s