Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Dependencies

Tags:

sql-server

Is there an easy way to chase down table/stored procedure/function dependencies in SQL Server 2005+? I've inherited a giant application with lots of tables and even more stored procedures and functions that are long and interlinked.

At the end of the day is there a way to build a dependency tree? Ideally what I'm looking for goes in both directions:

For a table/procedure - what depends ON it?: Show me all the stored procedures that eventually reference it (ideally in a tree view such that sub procedures nest out to the bigger procedures that call them)

For a procedure - what does IT depend on?: Show me all the procedures and tables that a given procedure will (or could) touch when running.

It seems this tool shouldn't be that hard to make and would be incredibly useful for DB maintenance generally. Is anyone aware of such a thing? If this doesn't exist, why the heck not?

The built-in functionality in Management Studio is nice but the information does not appear to be complete at all.

like image 392
Cory Avatar asked Nov 18 '08 15:11

Cory


People also ask

Where can I find dependencies in SQL Server?

Using SQL Server Management Studio In Object Explorer, expand Databases, expand a database, and then expand Tables. Right-click a table, and then click View Dependencies.

What are table dependencies?

A dependency is a constraint that applies to or defines the relationship between attributes. It occurs in a database when information stored in the same database table uniquely determines other information stored in the same table.

What is non schema bound dependency SQL Server?

Non-schema-bound dependency. Is a relationship between two objects that does not prevent the referenced object from being dropped or modified. Not available or Unresolved Entity.


1 Answers

Hopefully I'm not too late with this:

If your SQL Login has access to the sys schema in a particular database, you can use the sys.dependencies view to find all of an object's dependencies in one shot:

SELECT o.name, o.type_desc, p.name, p.type_desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
    ON d.object_id = o.object_id
INNER JOIN sys.objects p
    ON d.referenced_major_id = p.object_id

Using this as a starting point you could probably build a decent tool to create a dependency tree. There are also type specific views (e.g. sys.columns) that give more in depth information regarding each specific database object type; these could be used to provide contextual information on an object if necessary.

like image 152
Jeremy S Avatar answered Oct 19 '22 04:10

Jeremy S