SQL database refactoring - Finding external references by analyzing object dependencies

SQL Database refactoring becomes more complicated according to the degree of coupling in the database architecture; the more coupled things are coupled the greater the potential impact of any change. The simplest way to describe the concept of coupling is that if changing one module in one module of code or a program requires changes in another part of the same or external application, then coupling exists

Database coupling can exist with:

External, client application source code, in which case when a database schema is changed the source code within the application that accesses the changed portion of the schema also needs to be changed
External databases – database objects can be referenced in other databases on the same server as well as in databases on linked servers
Internal database references – Examples of intra-database dependencies include a column is coupled to any function, stored procedure, or view that references it, or to other table that uses the column as a foreign key

A single application environment is a lower risk scenario for database refactoring because the only couplings you need to deal are your internal references and your application source code

Dialog showing a single application environment

In addition to these couplings a database can be coupled with source code of other applications, and a wide variety of software systems

Dialog showing a multiple application environment

Other database couplings can be data load and data extract source code from other sources:

Persistence frameworks – automates the storage of program data into database and represents layer of abstraction between the application and the database
Data migration scripts – all scripted database objects will be referenced in the script and changes in a database will require changes in a database script
Test code – includes any code that that performs transactions that affect your database. All test codes will also had to be updated after a change in a database
Database documentation – when a database schema changes the documentation that describes it also needs to change accordingly

As we mentioned, the greater the coupling, the harder it becomes to complete refactoring, especially with a multi-application database. Reducing coupling comes down to reducing the amount of dependencies. The best way to reduce database coupling is to encapsulate database access

Database application dependencies

In this example we will have a simple C#.NET WinForm project that references the Employee table:

SqlConnection con = new SqlConnection("DataSource=MCA\\LENOVO;Initial Catalog=AdventureWorks2012;Integrated Security=True");
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter("select * from Employee", con);
sda.Fill(dt);
dataGridView1.DataSource = dt;

In case that the Employee table is changed in a database schema (renamed, vertically split etc.) you’ll need to refactor code in the application that references to the Employee table

You can find external references in an application using ApexSQL Clean, a tool that analyzes SQL Server database objects’ dependencies within the database, between external databases, scripts, and applications

When starting a new project in ApexSQL Clean under the Files tab ApexSQL Clean offers a possibility to include folders that contain project and project files for external references. Using the Add folder button folders containing Delphi, C++, ASP, or any other client code can be added to analysis, and using the Add project button a specific .sln, .csproj, or .vbproj file can be added

In this case we’ll add a project file of the .NET application that references the Employee table.

Other options under the Files tab include the File Name Patterns option that allows customizing and maintaining the list of files to be searched during the analysis process by filtering with regular expressions. For example, if the client code was written in C# then use a file name pattern .cs so only the files containing or ending with .cs are analyzed for SQL dependencies. To match the match the very last extension use the file pattern .cs$. Click the Add button and insert the pattern in the Add Pattern dialog:

Add Pattern dialog

The Comments to ignore option contains a list of comments from client code to be ignored. Click the Add button and in the Add Comment dialog choose the type of comment. In case of the multiline comment enter the open and close delimiters:

Add Comment dialog

All added comments will be set in the dependency analysis settings:

To make search for objects in source code files case sensitive use the Match case option and for matching the whole word only use the Match whole word only option

After setting up a project click the OK button to start analyzing dependencies. The object‘s references and information will be displayed in the Results grid:

The External refs column shows the number of references in external databases and included files

References for the included file(s) will be shown in the Children sub-grid for all objects. By choosing the Employee table in the Results grid we can see that the table is referenced in the .NET project we included in the analysis:

Cross database dependencies

If an entity is referenced by an object in another database that object also needs to be refactored to reflect the changes. For example the Employee table in the AdventureWorks2008R2 database is referenced by the uspGetEmployeeInformation stored procedure from the AdventureWorks2012 database:

CREATE PROCEDURE uspGetEmployeeInformation
AS
BEGIN
SELECT [AdventureWorks2008R2].[HumanResources].[Employee].[BusinessEntityID], 
[AdventureWorks2008R2].[HumanResources].[Employee].[JobTitle], 
[AdventureWorks2008R2].[HumanResources].[Employee].[HireDate], 
[AdventureWorks2008R2].[HumanResources].[Employee].[VacationHours] 
FROM [AdventureWorks2008R2].[HumanResources].[Employee]  
END

One way to find cross database object dependencies is using the sys.sql_expression_dependencies system view:

SELECT 
OBJECT_NAME(referencing_id) AS RefrencingObject 
,referenced_database_name AS DatabaseName
,referenced_schema_name AS SchemaName
,referenced_entity_name AS ReferencedObject
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID(N'dbo.uspGetEmployeeInformation');
GO

Dialog showing the the sys.sql_expression_dependencies system view

To use this approach you’ll need to specify an object for which you want to find referenced objects

To find cross-database dependencies using ApexSQL Clean, when you start a new project in the New Project dialog under the External Databases tab include all external databases you want to include into analysis: