The following gets foreign key constraints where one method gets all tables at once and the second one table only.
Hopefully if this is not an exact solution you it can provide another idea to work with via some code or sql modifications.
Original SQL came from this post
I used a Windows form app for visuals but does not need a user interface.
Container for constraint information
public class Constraints
{
public string TableName { get; set; }
public string ConstraintName { get; set; }
public string ColumnName { get; set; }
public string ReferencedTable { get; set; }
public string ReferencedColumn { get; set; }
public override string ToString() => TableName;
}
SQL Statements
public class QueryStatements
{
/// <summary>
/// Get all tables with foreign keys
/// </summary>
/// <returns></returns>
public static string ForeignKeysAllTables() =>
@"
SELECT
TableName = t.Name,constr.name AS ConstraintName, cols.name AS ColumnName, t2.name AS ReferencedTable, c2.name AS ReferencedColumn
FROM sys.tables t
INNER JOIN sys.foreign_keys constr ON constr.parent_object_id = t.object_id
INNER JOIN sys.tables t2 ON t2.object_id = constr.referenced_object_id
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = constr.object_id
INNER JOIN sys.columns cols ON cols.object_id = fkc.parent_object_id AND cols.column_id = fkc.parent_column_id
INNER JOIN sys.columns c2 ON c2.object_id = fkc.referenced_object_id AND c2.column_id = fkc.referenced_column_id
ORDER BY t.Name, cols.name;
";
/// <summary>
/// Get foreign keys for a single table
/// </summary>
/// <returns></returns>
public static string ForeignKeysForSingleTable() =>
@"
SELECT
TableName = t.Name,constr.name AS ConstraintName, cols.name AS ColumnName, t2.name AS ReferencedTable, c2.name AS ReferencedColumn
FROM sys.tables t
INNER JOIN sys.foreign_keys constr ON constr.parent_object_id = t.object_id
INNER JOIN sys.tables t2 ON t2.object_id = constr.referenced_object_id
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = constr.object_id
INNER JOIN sys.columns cols ON cols.object_id = fkc.parent_object_id AND cols.column_id = fkc.parent_column_id
INNER JOIN sys.columns c2 ON c2.object_id = fkc.referenced_object_id AND c2.column_id = fkc.referenced_column_id
WHERE t.name = @TableName
ORDER BY t.Name, cols.name;
";
/// <summary>
/// Get all table names in a database
/// </summary>
/// <param name="databaseName"></param>
/// <returns></returns>
public static string GetTableNames(string databaseName) =>
$"SELECT TABLE_NAME FROM [{databaseName}].INFORMATION_SCHEMA.TABLES " +
"WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME <> 'sysdiagrams' ORDER BY TABLE_NAME";
}
Data class
public class Informational
{
/// <summary>
/// For a real app the connection string could come
/// from app.config, adjust as needed
/// </summary>
private static string _databaseName = "NorthWind2020";
private static string _connectionString =
"Data Source=.\\SQLEXPRESS;" +
$"Initial Catalog={_databaseName};" +
"Integrated Security=True";
public static List<Constraints> GetAllTablesForeignConstraints()
{
var results = new List<Constraints>();
using (var cn = new SqlConnection {ConnectionString = _connectionString})
{
using (var cmd = new SqlCommand {Connection = cn})
{
cmd.CommandText = QueryStatements.ForeignKeysAllTables();
cn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
results.Add(new Constraints()
{
TableName = reader.GetString(0),
ConstraintName = reader.GetString(1),
ColumnName = reader.GetString(2),
ReferencedTable = reader.GetString(3),
ReferencedColumn = reader.GetString(4)
});
}
}
}
return results;
}
public static List<Constraints> GetTableForeignConstraints(string tableName)
{
var results = new List<Constraints>();
using (var cn = new SqlConnection { ConnectionString = _connectionString })
{
using (var cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText = QueryStatements.ForeignKeysForSingleTable();
cmd.Parameters.AddWithValue("@TableName", tableName);
cn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
results.Add(new Constraints()
{
TableName = reader.GetString(0),
ConstraintName = reader.GetString(1),
ColumnName = reader.GetString(2),
ReferencedTable = reader.GetString(3),
ReferencedColumn = reader.GetString(4)
});
}
}
}
return results;
}
public static List<string> GetTableNameList()
{
var results = new List<string>();
using (var cn = new SqlConnection {ConnectionString = _connectionString})
{
using (var cmd = new SqlCommand {Connection = cn})
{
cmd.CommandText = QueryStatements.GetTableNames(_databaseName);
cn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
results.Add(reader.GetString(0));
}
}
}
return results;
}
}
In a form, a ListBox is loaded with table names (does not mean they have constraints) and a TextBox to display information. On selection change of the ListBox with table names constraints are retrieved or a hard coded sample for one table via Button Click event.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SqlServerUtilities;
namespace SqlServerUtilitiesFrontEnd
{
public partial class Form1 : Form
{
private List<Constraints> _constraints;
public Form1()
{
InitializeComponent();
Shown += Form1_Shown;
}
private void Form1_Shown(object sender, EventArgs e)
{
_constraints = Informational.GetAllTablesForeignConstraints();
TableNameListBox.DataSource = Informational.GetTableNameList();
DisplayForeignConstraintNames();
TableNameListBox.SelectedIndexChanged += TableNameListBox_SelectedIndexChanged;
}
private void TableNameListBox_SelectedIndexChanged(object sender, EventArgs e)
{
DisplayForeignConstraintNames();
}
private void DisplayForeignConstraintNames()
{
var constraints = _constraints
.Where(item => item.TableName == TableNameListBox.Text).ToList();
var sb = new StringBuilder();
foreach (var constraint in constraints)
{
sb.AppendLine($"{constraint.ConstraintName}, {constraint.ReferencedColumn}");
}
ResultsTextBox.Text = sb.Length == 0 ? "None" : sb.ToString();
}
private void GetCustomersForeignConstraintsButton_Click(object sender, EventArgs e)
{
var constraints = Informational.GetTableForeignConstraints("Customers");
var sb = new StringBuilder();
foreach (var constraint in constraints)
{
sb.AppendLine($"{constraint.ConstraintName}, {constraint.ReferencedColumn}");
}
ResultsTextBox.Text = sb.Length == 0 ? "None" : sb.ToString();
}
}
}
I placed all classes in a class project as the Windows Form project is for demo purposes while the class project contains what is needed.
