In sql server, is there any way to check whether the schema change will impact on the stored procs?

Question

In SQL Server, is there any way to check whether the changes in the schema will impact Stored Procedures (and/or Views)?
For example a change of the column name in one table, may break some Stored Procedures; how to check the impacted stored procs?

Answer 1

Best way I can think to do this is to abstract your stored procedures from your actual tables using views, and to create those views with a "WITH SCHEMABINDING" clause which should prevent changes that will break your views...

Answer 2

Use Visual Studio Database Edition for your T-SQL development. It will catch such problems during build, as it creates the deployment .dbschema file.

Answer 3

Commercial tools such as Red Gate's SQL Refactor can do this.
I think that recent version of Visual Studio also include this kind of features, but I haven't tried.

To my knowledge, there are no built-in features of Microsoft SQL Server per-se which will do this. Correction: I just read about *sp_depends* in KM's answer to this post... Note that sp_depends's usage is deprecated; it is replaced by *sys.dm_sql_referencing_entities* and *sys.dm_sql_referenced_entities*

Also, if the underlying stored procedures use dynamic SQL, the task of detecting dependencies becomes more difficult and prone to "misses".

Answer 4

In SSMS (SQL Server Management Studio) right click on the object you are changing and click on View Dependencies. I don't think this will find references from another database.

You can also look for references in stored procedures if they are not encrypted. You would have to do this in each database you suspect might reference the object you are changing.

select objects.name ,sql_modules.definition from sys.sql_modules sql_modules join sys.objects objects on sql_modules.object_id = objects.object_id where definition like '%some column name%';

I have found nothing that is 100.0000% accurate 100.000000% of the time.

Answer 5

try using:

EXEC sp_depends 'YourTableName'

and/or

DECLARE @Search nvarchar(500)
SET @Search='YourTableName' --or anything else
SELECT DISTINCT
    LEFT(o.name, 100) AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1

Answer 6

Have a look at these answers:

http://stackoverflow.com/questions/2454763/refreshing-metadata-on-user-functions-t-sql

http://stackoverflow.com/questions/1839425/sql-server-relationships-buried-in-stored-procedures-rather-than-schema

http://stackoverflow.com/questions/1883700/in-sql-server-how-can-i-find-everywhere-a-column-is-referenced

http://stackoverflow.com/questions/1850045/how-do-i-find-all-stored-procedures-that-insert-update-or-delete-records

Other than dynamic SQL, using SCHEMABINDING where possible and sp_refreshsqlmodule and sql_dependencies for everything else is very accurate.