Skip to content

Instantly share code, notes, and snippets.

@krcourville
Created July 20, 2015 15:16
Show Gist options
  • Save krcourville/a4c2a5ad2392fcbaf22a to your computer and use it in GitHub Desktop.
Save krcourville/a4c2a5ad2392fcbaf22a to your computer and use it in GitHub Desktop.
Linqpad recursively print MSSQL table and related tables
const string connectionstring = @"Server=myserver;Database=mydb;Trusted_Connection=True;Connection Timeout=30;";
string dependsSql = @"
select t.name as TableWithForeignKey, fk.constraint_column_id as FK_columns , c.name as ForeignKeyColumn
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where fk.referenced_object_id = (select object_id from sys.tables where name = @tablename)
order by TableWithForeignKey";
SqlConnection connection = new SqlConnection(connectionstring);
HashSet<string> tablesRead = new HashSet<string>();
void Main()
{
DumpTablesDepends("mytable");
}
// Define other methods and classes here
void DumpTablesDepends(string tableName, int currentLevel = 0){
currentLevel++;
var table = new DataTable();
GetDataAdapter(tableName).Fill(table);
foreach (DataRow row in table.Rows)
{
var nextTableName = Convert.ToString(row["TableWithForeignKey"]);
if(!tablesRead.Contains(nextTableName)){
tablesRead.Add(nextTableName);
DumpTablesDepends(nextTableName, currentLevel);
}
}
if(table.Rows.Count > 0){
currentLevel.Dump("LEVEL");
table.Dump();
}
}
SqlDataAdapter GetDataAdapter(string tableName){
SqlCommand cmd = new SqlCommand(dependsSql, connection);
cmd.Parameters.AddWithValue("@tablename", tableName);
return new SqlDataAdapter(cmd);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment