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 pro开发者_运维技巧cs?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
Use Visual Studio Database Edition for your T-SQL development. It will catch such problems during build, as it creates the deployment .dbschema file.
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.
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:
Refreshing metadata on user functions t-SQL
SQL Server relationships buried in stored procedures rather than schema
In SQL Server, how can I find everywhere a column is referenced?
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.
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[1]', '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];
精彩评论