private static string GetSqlForForeignKeys(string parentTable, string childTable, string constraintName)
{
var sb = new StringBuilder();
sb.Append(" DECLARE @FKeys TABLE ");
sb.Append(" ( ");
sb.Append(" parentTable [Varchar] (100) NOT NULL, ");
sb.Append(" childTable [Varchar] (100) NOT NULL, ");
sb.Append(" childColumn [Varchar] (100) NOT NULL, ");
sb.Append(" constid int NOT NULL, ");
sb.Append(" keyno smallint NOT NULL ");
sb.Append(" ) ");
sb.Append(" DECLARE @PKeys TABLE ");
sb.Append(" ( ");
sb.Append(" parentTable [Varchar] (100) NOT NULL, ");
sb.Append(" childTable [Varchar] (100) NOT NULL, ");
sb.Append(" parentColumn [Varchar] (100) NOT NULL, ");
sb.Append(" constid int NOT NULL, ");
sb.Append(" keyno smallint NOT NULL ");
sb.Append(" ) ");
sb.Append(" INSERT INTO @FKeys ");
sb.Append(" SELECT DISTINCT ");
sb.Append(" parent.name parentTable, ");
sb.Append(" child.name childTable, ");
sb.Append(" syscolumns.name as childColumn, ");
sb.Append(" sysforeignkeys.constid, ");
sb.Append(" sysforeignkeys.keyno ");
sb.Append(" FROM ");
sb.Append(" sysforeignkeys ");
sb.Append(" inner join sysobjects child on fkeyid = child.id ");
sb.Append(" inner join sysobjects parent on rkeyid = parent.id ");
sb.Append(" inner join syscolumns on syscolumns.id = sysforeignkeys.fkeyid AND syscolumns.colorder = sysforeignkeys.fkey ");
sb.Append(" INSERT INTO @PKeys ");
sb.Append(" SELECT ");
sb.Append(" parent.name parentTable, ");
sb.Append(" child.name childTable, ");
sb.Append(" syscolumns.name as parentColumn, ");
sb.Append(" sysforeignkeys.constid, ");
sb.Append(" sysforeignkeys.keyno ");
sb.Append(" FROM ");
sb.Append(" sysforeignkeys inner join sysobjects child on fkeyid = child.id ");
sb.Append(" inner join sysobjects parent on rkeyid = parent.id ");
sb.Append(" inner join syscolumns on syscolumns.id = sysforeignkeys.rkeyid AND syscolumns.colorder = sysforeignkeys.rkey ");
sb.Append(" SELECT p.parentTable ,p.parentColumn, f.childTable, f.ChildColumn , so.name as roleName FROM @FKeys f INNER JOIN @PKeys p on f.constid=p.constID and f.keyno=p.keyno INNER JOIN sysobjects so on so.id = p.constid ");
sb.Append("WHERE f.parentTable = '" + parentTable + "' AND f.childTable = '" + childTable + "'");
sb.Append(" AND so.name = '" + constraintName + "'");
sb.Append(" order by p.constid ");
return sb.ToString();
}
