Search code examples
sql-serverrefactoringschemarefactoring-databases

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

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?

Solution

  • 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.