In some cases, we need to make changes to our table structure. One of the challenges that might arise when making changes to table structure is that we need to make sure to review the database objects (stored procedures, views, and user defined functions) that might be affected by the table structure changes that we made. If there is a good documentation in place, we might be able to find the affected database objects pretty easily (this is a good case for having a good design/technical documentation). But since that's not always the case, we need to have a way to find the list of potentially affected database objects.
SQL Management Studio
On our SQL Server Management Studio, we can identify the database objects (stored procedures, views, constraints and user defined functions) associated with a particular table pretty easily. To do that, open SQL Server Management Studio, and on the Object Explorer window, right click on the table that you are going to modify. On the context menu, select View Dependencies. It should open a window that list all the database objects that depend on that particular table. 
Stored Procedures/ Functions
There are also stored procedure/functions that you can use to find the object dependencies. If you are still using SQL Server 2005, you can use sp_depends stored procedure. It is very easy to use. For example let say that you want to find the list of database objects that depend on HumanResources.Employee table in AdventureWorks database. You can use the following:
USE [AdventureWorks];
EXEC sp_depends 'HumanResources.Employee';
If you are using SQL Server 2008, you can use the sys.dm_sql_referencing_entities.
Alternatives
For most part, using SQL Management Studio or sp_depends or sys.dm_sql_referencing_entities should be sufficient. However, in situation where we are using dynamic SQL in our stored procedure, we will need a different approach. One such approach would be to do a text search on the database object definition.
Back to the example of finding the list of database objects that depends on HumanResources.Employee table in AdventureWorks database, we can try to use the following script:
USE [AdventureWorks];
SELECT B.Name AS ObjectName, B.Type_Desc AS ObjectType
FROM sys.sql_modules A
INNER JOIN sys.objects B ON A.Object_ID = B.Object_ID
WHERE REPLACE(REPLACE(A.[Definition],'[', ''), ']', '') LIKE '%HumanResources.Employee%';
The above script has its own limitation. Since it uses wildcard text search, it might return more database objects than it really needs. In our example, it might also returns database objects that contains HumanResources.EmployeeAddress, HumanResources.EmployeeDepartmentHistory, etc.
Another problem that might arise when trying to find object dependencies is that if we use the View Dependencies on SQL Management Studio Object Explorer, or sp_depend, we can only see the object dependencies limited to that one particular database. It could happen that you have a tool database, and within that tool database you have a stored procedure that uses the table that you want to modify. So we need to have a way to search across databases. One way to do that, we can use the following script:
DECLARE @ObjectDependencies TABLE (ObjectName VARCHAR(255), ObjectType VARCHAR(255), DatabaseName VARCHAR(255))
INSERT INTO @ObjectDependencies
EXEC sp_msforeachdb 'SELECT B.Name AS ObjectName, B.Type_Desc AS ObjectType, ''?'' AS [Database] FROM ?.sys.sql_modules A INNER JOIN ?.sys.objects B on A.Object_ID = B.Object_ID WHERE REPLACE(REPLACE(A.[Definition],''['', ''''), '']'', '''') LIKE ''%HumanResources.Employee%'''
SELECT ObjectName, ObjectType, DatabaseName
FROM @ObjectDependencies
Third Party Tools
There are also some third-party tool that you can use to search for text on database objects (as a way to find object dependencies). One of the tool that I have been using is SQL Digger (http://www.sqldigger.com). It was pretty neat tool that can be use to search text on database object.
No comments:
Post a Comment