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?
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.
EXEC sp_depends 'YourTableName'
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
Use Visual Studio Database Edition for your T-SQL development. It will catch such problems during build, as it creates the deployment .dbschema file.
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...
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".
If you want to change the name of an object or column, then the Smart Rename feature of Red Gate Software's SQL Prompt 5 will generate a script that both performs the rename and updates references to the old name in other objects.
If you're just interested in what depends on a column name, then SQL Prompt 5 also has a Column Dependencies function, where hovering over the column name in a script pops up a window containing a list of objects that refer to the column.
You can download a 14-day trial for free, to see if either of these features works for you.
Paul Stephenson SQL Prompt Project Manager Red Gate Software
Have a look at these answers:
Other than dynamic SQL, using SCHEMABINDING where possible and sp_refreshsqlmodule and sql_dependencies for everything else is very accurate.
If you use SQL Server
You can use this query after your change and find Stored Procedure Or View Or ...
that after your change might get error
USE <Your_DataBase_Name>; SET NOCOUNT ON; DECLARE @name NVARCHAR(MAX) DECLARE @sql NVARCHAR(MAX) DECLARE @type CHAR(2) DECLARE @type_desc NVARCHAR(60) DECLARE @params NVARCHAR(MAX) DECLARE @tblInvalid TABLE ( [type_desc] NVARCHAR(60) , [name] NVARCHAR(MAX) , [error_number] INT , [error_message] NVARCHAR(MAX) , [type] CHAR(2) ); DECLARE testSPs CURSOR FAST_FORWARD FOR SELECT [name] = OBJECT_NAME(SM.[object_id]) , [type] = SO.[type] , SO.[type_desc] , [params] = ( SELECT ( SELECT CONVERT(XML, ( SELECT STUFF(( SELECT ', ' + [name] + '=NULL' AS [text()] FROM sys.parameters WHERE [object_id] = SM.[object_id] FOR XML PATH('') ), 1, 1, '') )) FOR XML RAW , TYPE ).value('/row', 'varchar(max)') ) FROM sys.sql_modules SM JOIN sys.objects SO ON SO.[object_id] = SM.[object_id] WHERE SO.[is_ms_shipped] = 0 AND SO.[type] = 'P' OPEN testSPs FETCH NEXT FROM testSPs INTO @name, @type, @type_desc, @params WHILE ( @@FETCH_STATUS = 0 ) BEGIN BEGIN TRY SET @sql = 'SET FMTONLY ON; exec ' + @name + ' ' + @params + '; SET FMTONLY OFF;' --PRINT @sql; EXEC (@sql); END TRY BEGIN CATCH PRINT @type_desc + ', ' + @name + ', Error: ' + CAST(ERROR_NUMBER() AS VARCHAR) + ', ' + ERROR_MESSAGE(); INSERT INTO @tblInvalid SELECT @type_desc , @name , ERROR_NUMBER() , ERROR_MESSAGE() , @type; END CATCH FETCH NEXT FROM testSPs INTO @name, @type, @type_desc, @params END CLOSE testSPs DEALLOCATE testSPs SELECT [type_desc] , [name] , [error_number] , [error_message] FROM @tblInvalid ORDER BY CHARINDEX([type], ' U V PK UQ F TR FN TF P SQ ') , [name];