Sample Header Ad - 728x90

SQLSMO Column Dependencies

2 votes
0 answers
169 views
I'm trying to help a teammate walk object dependencies in SQL Server. I've had *some* success using things like sys.sql_expression_dependencies (and other related DMV/DMFs) but it's falling short due to a lot of weird edge cases (cross database references, inconsistent naming conventions, etc.) I'm looking into using SqlSMO to walk object dependencies, and so far, it's working pretty well; at least for object dependencies. I give it a procedure name, and use a DependencyWalker to tell me things like views, functions and procedures called therein. But now I need to also know which columns are referenced in the proc. So instead of just knowing my inputProc references someTable, I'd like to know that it references someTable.A and someTable.B. So far I haven't been able to find a way to do this using SMO. Anyone know if it can be done? This is an excerpt of C# code which returns the object references; not sure if and how to modify it to include column-level dependencies as well. public static void Walk() { Server server = new Server(@"localhost\koala"); Database db = server.Databases["test"]; Scripter scr = new Scripter(server); scr.Options.WithDependencies = true; scr.Options.NoViewColumns = false; Urn[] urns = new Urn[] { db.StoredProcedures["xdb"].Urn }; DependencyTree dt = scr.DiscoverDependencies(urns, true); DependencyWalker dw = new DependencyWalker(server); DependencyCollection dc = dw.WalkDependencies(dt); foreach (var item in dc) { Console.WriteLine(item.Urn.Value); } }
Asked by Xedni (141 rep)
Jul 23, 2018, 07:54 PM