Getting metadata - the matchink PK for a FK

[David Thielen]

Hi;

We need to get the matching table.column primary key for each foreign
key. What we are presently using, which is so very very slow is:

ver 8:
// get the constraint - if one exists
int id;
using (DbCommand cmd =
provider.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType =
CommandType.Text;
cmd.CommandText = "SELECT
sysobjects.id FROM sysobjects INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE " +
"ON sysobjects.name =
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.Constraint_Name WHERE " +
"(column_name = @p1)
AND (Table_Name = @p2) AND (sysobjects.xtype = 'F')";

cmd.Parameters.Add(MakeParam("@p1", colOn.Name));

cmd.Parameters.Add(MakeParam("@p2", table));
using (DbDataReader myReader =
cmd.ExecuteReader())
{
if (!myReader.Read())
continue;
id =
myReader.GetInt32(0);
}
}

// ok, we have it - go get it.
int rkeyid, rkey;
using (DbCommand cmd =
provider.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType =
CommandType.Text;
cmd.CommandText = "SELECT
rkeyid, rkey FROM sysforeignkeys WHERE constid = @p1";

cmd.Parameters.Add(MakeParam("@p1", id));
using (DbDataReader myReader =
cmd.ExecuteReader())
{
myReader.Read();
rkeyid =
myReader.GetInt32(0);
rkey =
myReader.GetInt16(1);
}
}

string pkTable;
using (DbCommand cmd =
provider.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType =
CommandType.Text;
cmd.CommandText = "select name
from sysobjects where id = @p1";

cmd.Parameters.Add(MakeParam("@p1", rkeyid));
using (DbDataReader myReader =
cmd.ExecuteReader())
{
myReader.Read();
pkTable =
myReader.GetString(0);
}
}

using (DbCommand cmd =
provider.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType =
CommandType.Text;
cmd.CommandText = "SELECT
Column_Name from INFORMATION_SCHEMA.COLUMNS where table_name=@p1 and
Ordinal_position=@p2";

cmd.Parameters.Add(MakeParam("@p1", pkTable));

cmd.Parameters.Add(MakeParam("@p2", rkey));
string pkColumn;
using (DbDataReader myReader =
cmd.ExecuteReader())
{
myReader.Read();
pkColumn =
myReader.GetString(0);
}

// got it!
return pkTable + "." +
pkColumn;

Version 9+:
// get the constraint - if one exists
ArrayList names = new ArrayList();
using (DbCommand cmd =
provider.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType =
CommandType.Text;
cmd.CommandText = "SELECT
Constraint_Name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE " +
"WHERE
(column_name=@p1) AND (Table_Name=@p2)";

cmd.Parameters.Add(MakeParam("@p1", colOn.Name));

cmd.Parameters.Add(MakeParam("@p2", table));
using (DbDataReader myReader =
cmd.ExecuteReader())
{
while
(myReader.Read())

names.Add(myReader.GetString(0));
}
}

int id = -1;
foreach (string name in names)
using (DbCommand cmd =
provider.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType =
CommandType.Text;
cmd.CommandText =
"SELECT object_id FROM sys.objects WHERE (name = @p1) AND (type='F')";

cmd.Parameters.Add(MakeParam("@p1", name));
using (DbDataReader
myReader = cmd.ExecuteReader())
{
if
(!myReader.Read())

continue;
id =
myReader.GetInt32(0);
break;
}
}
if (id == -1)
continue;

// ok, we have it - go get it.
int rkeyid, rkey;
using (DbCommand cmd =
provider.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType =
CommandType.Text;
cmd.CommandText =
"SELECT
referenced_object_id, referenced_column_id FROM
sys.foreign_key_columns WHERE constraint_object_id = @p1";

cmd.Parameters.Add(MakeParam("@p1", id));
using (DbDataReader myReader =
cmd.ExecuteReader())
{
myReader.Read();
rkeyid =
myReader.GetInt32(0);
rkey =
myReader.GetInt32(1);
}
}

string pkTable;
using (DbCommand cmd =
provider.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType =
CommandType.Text;
cmd.CommandText = "select name
from sys.objects where object_id = @p1";

cmd.Parameters.Add(MakeParam("@p1", rkeyid));
using (DbDataReader myReader =
cmd.ExecuteReader())
{
myReader.Read();
pkTable =
myReader.GetString(0);
}
}

using (DbCommand cmd =
provider.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType =
CommandType.Text;
cmd.CommandText =
"SELECT Column_Name
from INFORMATION_SCHEMA.COLUMNS where table_name=@p1 and
Ordinal_position=@p2";

cmd.Parameters.Add(MakeParam("@p1", pkTable));

cmd.Parameters.Add(MakeParam("@p2", rkey));
string pkColumn;
using (DbDataReader myReader =
cmd.ExecuteReader())
{
myReader.Read();
pkColumn =
myReader.GetString(0);
}

// got it!
return pkTable + "." +
pkColumn;


Is there a faster way to do this?

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Trevor Benedict]

http://www.bytestopshere.com/post.cfm/sql-server-script-to-find-foreign-key-dependencies

Regards,

Trevor Benedict
MCSD

 

[David Thielen]

Wow - that is an amazing select - good job. Two follow on questions:

1) Is this stil the fastest way to read this? We need to do this a
lot.

2) What's with the *_column_2 part? Do they sometimes have pairs that
have to match? And if so, when would you use that and how would you
write the join?

thanks - dave

http://www.bytestopshere.com/post.cfm/sql-server-script-to-find-foreign-key-dependencies

Regards,

Trevor Benedict
MCSD

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[David Thielen]

ps - Does this run on all versions of Sql Server from Sql Server 2000
on?

thanks - dave

http://www.bytestopshere.com/post.cfm/sql-server-script-to-find-foreign-key-dependencies

Regards,

Trevor Benedict
MCSD

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Trevor Benedict]

This partcular query runs against the base system tables which has been
there since sybase 10. It is backward comptaible.

Regards,

Trevor Benedict