If you need to make small changes to an existing database object, then you will need to do some impact assessment to determine what SQL Server Objects will be affected by your small change. So how do you identify those database objects that will be affected by a scheme change? Is there a simple way to identify the store, procedures, triggers, views, etc. that might be affected when you implement a database change? Some shops have the luxury of still having the original developer on site that retained the intimate knowledge of the original design. Although in this day when IT staff move to new jobs frequently most IT shops are not so lucky to have a resident expert. Possibly other shops have documentation that identifies object dependencies, but my guess is these shops are few and far between. For these reasons, you need to have a way to programmatically find objects that are affected by any minor schema changes you might make. This article will discuss a couple of approaches you might use to help with your impact analysis when you are contemplating a database schema change.
Using the SYSCOMMENTS table
The actual code for views, rules, defaults, triggers, CHECK constraints, DEFAULT constraints and stored procedures are stored in the syscomments table. The column TEXT in the syscomments table contains the actual code for all these objects. Knowing this allows you to write some simple T-SQL code that can scan the syscomments table looking for an actual table column name or database object name that you are planning on changing. By scanning the syscomments table, you will be able to narrow the focus of your impact analysis down to only those objects that might be affected by your change.
Let me demonstrate how you might query the syscomments table to identify objects that are affected by changing a column in a database table. For this example let’s assume I am going to make a change to the column “pub_id” in the “titles” table stored in the “pubs” database. In this case, to identify all the objects that use the “pub_id” column all I need to do is run the following query:
use pubs
go
select name from syscomments c join sysobjects o
on c.id = o.id
where TEXT like ‘%pub_id%’ and TEXT like ‘%Titles%’
This query looks for syscomments rows that contain both the string “pub_id” and “titles” in the same record. With this query, I find all syscomments records that contain both a reference to the table and column that I planned on changing. This query joins the syscomments and sysobjects table so that the object name can be displayed in the results. When I run this command against the pubs database, on my server, I find that the following four objects reference the “pub_id” column in the “titles” table in the pubs database: titleview, reptq1, reptq2, reptq3.
This method of searching for a string of code in the TEXT column of the syscomments table can be used for more than just finding table names and column names that are used by an object. You can also use this method to find any string of code in any object. If I want to find all the objects in the Northwind database that perform a “DELETE” command then the following code could be run:
select name from Northwind.dbo.syscomments c join Northwind.dbo.sysobjects o
on c.id = o.id
where TEXT like ‘%delete%’
Using this method is an easy and quick way of searching for any object that contains a fragment of code.
Using the sp_depends Stored Procedure
SQL Sever maintains a database system table named sysdepends. SQL Server uses this table to store object dependencies. This table only contains information about objects that are dependent upon other objects. It does not contain detailed dependency information, such as which attributes of an object are referenced by other objects. Even so, you can use the information in the sysdepends table to help narrow down your impact analysis when you are making a schema change.
To access the sysdepends table information SQL Server provides the sp_depends system stored procedure. Here is an example of how to use this stored procedure:
USE pubs
EXEC sp_depends ‘Titles’
When I run this statement against my pubs database the following information is returned:
In the current database, the specified object is referenced by the following:
name type
——————————————– —————-
dbo.reptq1 stored procedure
dbo.reptq2 stored procedure
dbo.reptq3 stored procedure
dbo.titleview view
As you can see, my example returns all objects that are dependent on the table name “Titles.” In this case, there are three stored procedures and one view. This method of using the sp_depends system stored procedure identified the same set of objects as my custom T-SQL code above that queried the “syscomments” table.
Not All Code is Stored in SQL Server
Now keep in mind the syscomments and sp_depends methods may not find all your code that is affected by a schema change. These two methods are only able to scan objects that are stored in SQL Server (views, rules, defaults, triggers, CHECK constraints, DEFAULT constraints, and stored procedures). If you have written your application code in such a way that allows you to issue T-SQL SELECT, UPDATE, INSERT and DELETE statements in code blocks that are not stored in SQL Server, then you will need to use other methods for scanning this code. Hopefully where you store the code for your application outside SQL Server is in a repository or file structure of some kind where you can do a global find or search of some kind.
Conclusion
When you are making a database schema change, it is important to identify all the objects that might be affected by the change. Without doing a complete impact assessment, you run the risk of causing problems when implementing your schema change. Therefore having some easy, and automated method to help identify the objects that will be impacted by your schema change is critical, and a time saver. The next time you are doing some impact analysis associated with a schema change to your database, you might consider using one or all the methods I discussed to help narrow down the objects impacted by your proposed change.
