Created
July 20, 2015 15:16
-
-
Save krcourville/a4c2a5ad2392fcbaf22a to your computer and use it in GitHub Desktop.
Linqpad recursively print MSSQL table and related tables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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