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)

Lackey

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.

Lackey

My reading of MSDN is that it does what yo want (having just looked, having failed to notice this addition!) – Primateship

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

Ester

Answer 3

This query should work, and could be executed using Database.ExecuteWithResults

Select fk.Table_Name from 
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
      ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
where PK.Table_Name = 'SomeTable'

e.g.

SqlConnection sqlConnection =
new SqlConnection(@"Integrated Security=SSPI; Data Source=SomeInstance");
Server server = new Server(serverConnection);
Database db = server.Databases["somedatabase"];
DataSet ds = db.ExecuteWithResults(thesqlabove);

Nephoscope

Answer 4

You could use the INFORMATION_SCHEMA Views.

INFORMATION_SCHEMA.TABLE_CONSTRAINTS will give you the names of the primary keys on that table.

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = @TableName

Given the primary key names you can get the referential constraints that use those keys from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

And then the table names by querying INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

Not SMO as such, but given the above you should be able to put together a query that will list the constraints you need to disable.

Primateship

Answer 5

It doesn't work for me.

Consider the following relations:

Table1 --> master table; Table2 --> slave table;

Table2.Table1_ID is a foreign key of Table1.ID

Table1.EnumForeignKeys() return null.

Instead I tried with success the DependencyWalker object. The following code list all the tables which dipend from a given collection of tables.

            DependencyWalker w = new DependencyWalker(db.Parent);
            DependencyTree tree = w.DiscoverDependencies(urns,false);
            DependencyCollection depends = w.WalkDependencies(tree);

            foreach (DependencyCollectionNode dcn in depends)
            {
                if (dcn.Urn.Type == "Table")
                {
                    dcn.Urn.GetNameForType("Table");
                    Console.WriteLine(dcn.Urn.GetNameForType("Table"));
                }
            }

where "urns" is a collection of table.Urn.

Itacolumite

Answer 6

You will have to travel through dependency tree. Following is the script which use the SMO to generate Create table and insert script.

**

**ServerConnection conn = new ServerConnection( GetConnection() );
            Server server = new Server( conn );
            Database db = server.Databases[ mDestinationDatabase ];
            // Create database script 
            StringBuilder dbScript = new StringBuilder();
            ScriptingOptions dbCreateOptions = new ScriptingOptions();
            dbCreateOptions.DriAll = true;
            dbCreateOptions.NoCollation = true;
            StringCollection coll = db.Script( dbCreateOptions );
            foreach( string str in coll )
            {
                dbScript.Append( str );
                dbScript.Append( Environment.NewLine );
            }
            sqlInsertCommands = dbScript.ToString();
            // Create dependency tree
            DependencyWalker w = new DependencyWalker(db.Parent);
            UrnCollection urnCollection = new UrnCollection();
            DataTable table = db.EnumObjects( DatabaseObjectTypes.Table );
            string tableName = string.Empty;
            foreach( DataRow row in table.Rows ) 
            {
                urnCollection.Add( new Urn( ( string )row[ "Urn" ] ) ); 
            }
            DependencyTree tree = w.DiscoverDependencies( urnCollection, true );
            DependencyCollection depends = w.WalkDependencies(tree); 
            // walk through the dependency tree and for each table generate create and insert scripts
            foreach (DependencyCollectionNode dcn in depends)
            {
                if (dcn.Urn.Type == "Table")
                {
                    tableName = dcn.Urn.GetNameForType( "Table" );
                     DataTable dataTableWithData = GetTableWithData( tableName);
                     ArrayList columnList = new ArrayList();
                    foreach(DataColumn  dataColumn in  dataTableWithData.Columns)
                    {
                        columnList.Add( dataColumn.ColumnName );
                    }
                    sqlInsertCommands = sqlInsertCommands + Environment.NewLine + Environment.NewLine
                        + GetCreateTableScript(tableName ) 
                        + Environment.NewLine + Environment.NewLine
                        + BuildInsertSQL( columnList, dataTableWithData, tableName );
                    }
            }**

**

Lunatic