В SQL Server есть ли способ проверить, повлияют ли изменения в схеме на хранимые процедуры (и / или представления)?
Например, изменение имени столбца в одной таблице может нарушить работу некоторых хранимых процедур; как проверить затронутые сохраненные процессы?
В sql server есть ли способ проверить, повлияет ли изменение схемы на сохраненные процессы?
Ответы (8)
В SSMS (SQL Server Management Studio) щелкните правой кнопкой мыши объект, который вы изменяете, и выберите «Просмотр зависимостей». Не думаю, что здесь можно найти ссылки из другой базы данных.
Вы также можете искать ссылки в хранимых процедурах, если они не зашифрованы. Вам придется сделать это в каждой базе данных, которая, как вы подозреваете, может ссылаться на объект, который вы изменяете.
выберите objects.name, sql_modules.definition из sys.sql_modules sql_modules присоединить объекты sys.objects к sql_modules.object_id = objects.object_id, где определение типа '% some column name%';
Я не нашел ничего, что было бы точным на 100,0000% в 100,000000% случаев.
попробуйте использовать:
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
Используйте Visual Studio Database Edition для разработки T-SQL. Он выявляет такие проблемы во время сборки, поскольку создает файл развертывания .dbschema.
На мой взгляд, лучший способ сделать это - абстрагировать ваши хранимые процедуры от ваших реальных таблиц с помощью представлений и создавать эти представления с помощью предложения «WITH SCHEMABINDING», которое должно предотвращать изменения, которые нарушат ваши представления ...
Коммерческие инструменты, такие как Red Gate's SQL Refactor a > может это сделать.
Я думаю, что последняя версия Visual Studio также включает такие функции, но я не пробовал.
Насколько мне известно, в Microsoft SQL Server как таковой нет встроенных функций, которые могли бы это сделать. Исправление: я только что прочитал о sp_depends в ответе KM на этот пост ... Обратите внимание, что использование sp_depends не рекомендуется; он заменяется на sys.dm_sql_referencing_entities и sys.dm_sql_referenced_entities
Кроме того, если лежащие в основе хранимые процедуры используют динамический SQL, задача обнаружения зависимостей становится более сложной и склонной к "ошибкам".
Если вы хотите изменить имя объекта или столбца, используйте функцию Умное переименование SQL Prompt 5 сгенерирует сценарий, который выполняет переименование и обновляет ссылки на старое имя в других объектах.
Если вас просто интересует, что зависит от имени столбца, тогда SQL Prompt 5 также имеет функцию Зависимости столбца, где при наведении курсора на имя столбца в скрипте появляется окно, содержащее список объектов. которые относятся к столбцу.
Вы можете бесплатно загрузить 14-дневную пробную версию, чтобы узнать, работает ли какая-либо из этих функций для вас.
Пол Стивенсон
Менеджер проекта SQL Prompt
Red Gate Software
Взгляните на эти ответы:
Обновление метаданных пользовательских функций t-SQL
а>
Как найти в SQL Server везде, где есть ссылка на столбец?
Как найти все сохраненные процедуры, которые вставляют, обновляют или удаляют записи?
За исключением динамического SQL, использование SCHEMABINDING там, где это возможно, и sp_refreshsqlmodule и sql_dependencies для всего остального очень точны.
Если вы используете SQL Server
Вы можете использовать этот запрос после внесения изменений и найти хранимую процедуру или просмотр или ...
что после вашего изменения может появиться ошибка
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];