Applies to:
Summary
In this article, it will be explained how to get SQL dependencies between objects that are in two different databases.
Description
ApexSQL Analyze is ideal for customers who want to see graphical SQL dependencies between objects or just a visual representation of objects from one or more databases.
Let’s say that during development, two databases are created with objects that have different relationships with each other. Names of databases are Database1 and Database2 and they have different tables that are connected through a view. The main goal is to understand object dependencies using this third-party dependency viewer tool.
So, let’s see SQL dependencies between the objects from these two different databases.
Loading databases
When the tool is launched for the first time, click the New button from the Home tab to load a new database:
The Project options window will open. In the Data source tab, select the desired SQL Server, type of the authentication, and then select the targeted database. Previously mentioned Database1 will be used for this example:
The next step is to move on to the External databases tab where all databases from the selected SQL Server will be listed in the database sections. Select the checkbox near the targeted database and click the OK button to proceed:
The diagram window will be opened showing database objects with dependencies:
As shown on the image, the dbo.v_Staff_Workplace view joins the dbo.Staff table from the same database Database1 and the dbo.Workplace table from another database which can be seen by Databese2 in front of the dbo.Workplace table name.
Notice that database objects are shown with different colors to easier distinguish them. By default, tables are represented by blue color, views are red, and users are a green color.
Dependencies are the arrows between objects in the main diagram. The direction of the arrow indicates which object it depends on. In this case, it’s shown that dbo.v_Staff_Workplace view depends on the dbo.Staff and Database2.dbo.Worklpace tables. The green color shows that a SELECT statement is used in the view, also labeled Executable.
The dependencies that are shown graphically can also be seen by hovering the mouse cursor over desired database objects. Data that is shown is an information number Referenced by objects, an object that is Referencing, Shema, and Type:
Object browser
A database object that is loaded from another database in Object browser will be named with the prefix of the database name, in this case, it is Database2.dbo.Workplace:
Dependencies panel
There is an additional way to see SQL dependencies. By clicking on the Dependencies button from the View tab, the Dependencies panel will be shown:
For example, by clicking on the desired database object, it can be seen that Database2.dbo.Workplace is referenced by Executable relationship type (SELECT statement) with dbo.v_Staff_Workplace:
As shown in the previous examples, there are two ways to represent SQL dependencies between database objects. The first is a graphical representation on the main diagram and the second is dependencies panel which is a tree view representation.
FAQs
Q: I noticed that in my database there are other colors of dependencies (arrows) that indicate Reference by and Referencing (Blue and Red) what exactly those colors represent?
A: As shown in the image below:
- The red arrow represents that object is referencing or referenced by with Schema relationship type (1)
- The blue arrow represents that object is referencing or referenced by with Foreign key relationship type (2)
Q: Can I select all Referenced or Referencing objects?
A: Yes, this can be done using the right-click context menu over the targeted database object by selecting the desired option:
Q: Can I see more information regarding the dependencies?
A: Yes. By default, the dependency depth value is set to 1. To see more in-depth, click on the Options button from the Home tab, enter 2 or 3 value for the Dependency depth:
