Как проверить все хранимые процедуры в SQL Server, если я отбрасываю таблицу или поля?
Как проверить всю хранимую процедуру в SQL Server?
Ответ 1
Я нашел ответ Cade полезным при формулировании моего собственного script для проверки объектов в базе данных, поэтому я подумал, что поделился бы и с моим script:
DECLARE @Name nvarchar(1000);
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;
DECLARE ObjectCursor CURSOR FAST_FORWARD FOR
SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id))
FROM sys.objects o
WHERE type_desc IN (
'SQL_STORED_PROCEDURE',
'SQL_TRIGGER',
'SQL_SCALAR_FUNCTION',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'VIEW')
--include the following if you have schema bound objects since they are not supported
AND ISNULL(OBJECTPROPERTY(o.object_id, 'IsSchemaBound'), 0) = 0
;
OPEN ObjectCursor;
FETCH NEXT FROM ObjectCursor INTO @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @Name + '''';
--PRINT @Sql;
BEGIN TRY
EXEC @Result = sp_executesql @Sql;
IF @Result <> 0 RAISERROR('Failed', 16, 1);
END TRY
BEGIN CATCH
PRINT 'The module ''' + @Name + ''' does not compile.';
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH
FETCH NEXT FROM ObjectCursor INTO @Name;
END
CLOSE ObjectCursor;
DEALLOCATE ObjectCursor;
Ответ 2
Он не поймает все (динамические SQL или поздние объекты), но может быть полезно - вызовите sp_refreshsqlmodule для всех хранимых процедур, не связанных с схемой (вы можете вызвать его раньше, чтобы убедиться, что зависимости обновлены, а затем запросить зависимости, или называть его потом и посмотреть, не сломалось ли что-либо):
DECLARE @template AS varchar(max)
SET @template = 'PRINT ''{OBJECT_NAME}''
EXEC sp_refreshsqlmodule ''{OBJECT_NAME}''
'
DECLARE @sql AS varchar(max)
SELECT @sql = ISNULL(@sql, '') + REPLACE(@template, '{OBJECT_NAME}',
QUOTENAME(ROUTINE_SCHEMA) + '.'
+ QUOTENAME(ROUTINE_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
+ QUOTENAME(ROUTINE_NAME)),
N'IsSchemaBound') IS NULL
OR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
+ QUOTENAME(ROUTINE_NAME)),
N'IsSchemaBound') = 0
EXEC (
@sql
)
Ответ 3
В основном я делал то же самое, но писал, что это CURSORless, который очень быстрый.
DECLARE @Name nvarchar(1000);
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;
DECLARE @Objects TABLE (
Id INT IDENTITY(1,1),
Name nvarchar(1000)
)
INSERT INTO @Objects
SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id))
FROM sys.objects o
WHERE type_desc IN (
'SQL_STORED_PROCEDURE',
'SQL_TRIGGER',
'SQL_SCALAR_FUNCTION',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'VIEW')
--include the following if you have schema bound objects since they are not supported
AND ISNULL(OBJECTPROPERTY(o.object_id, 'IsSchemaBound'), 0) = 0
DECLARE @x INT
DECLARE @xMax INT
SELECT @xMax = MAX(Id) FROM @Objects
SET @x = 1
WHILE @x < @xMax
BEGIN
SELECT @Name = Name FROM @Objects WHERE Id = @x
SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @Name + '''';
--PRINT @Sql;
BEGIN TRY
EXEC @Result = sp_executesql @Sql;
IF @Result <> 0 RAISERROR('Failed', 16, 1);
END TRY
BEGIN CATCH
PRINT 'The module ''' + @Name + ''' does not compile.';
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH
SET @x = @x + 1
END
Ответ 4
В дополнение к script от Майкла Петито вы можете проверить проблемы с поздними объектами в SP (отложенное разрешение имен) следующим образом:
-- Based on comment from http://blogs.msdn.com/b/askjay/archive/2012/07/22/finding-missing-dependencies.aspx
-- Check also http://technet.microsoft.com/en-us/library/bb677315(v=sql.110).aspx
select o.type, o.name, ed.referenced_entity_name, ed.is_caller_dependent
from sys.sql_expression_dependencies ed
join sys.objects o on ed.referencing_id = o.object_id
where ed.referenced_id is null
Ответ 5
Пара способов, которые приходят на ум
- Наиболее очевидный способ выполнения процедур
- проверяйте зависимости таблицы перед тем, как вы отбросите таблицу или поле. затем проверьте эти зависимые proceudres
- генерировать скрипты для всех процедур и искать это поле или таблицу
- Query sysobjects
Ответ 6
Я попробовал "Cade Roux" Ответ: все пошло не так, и я исправил его как
SELECT 'BEGIN TRAN T1;' UNION
SELECT REPLACE('BEGIN TRY
EXEC sp_refreshsqlmodule ''{OBJECT_NAME}''
END TRY
BEGIN CATCH
PRINT ''{OBJECT_NAME} IS INVALID.''
END CATCH', '{OBJECT_NAME}',
QUOTENAME(ROUTINE_SCHEMA) + '.'
+ QUOTENAME(ROUTINE_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
+ QUOTENAME(ROUTINE_NAME)),
N'IsSchemaBound') IS NULL
OR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
+ QUOTENAME(ROUTINE_NAME)),
N'IsSchemaBound') = 0
UNION
SELECT 'ROLLBACK TRAN T1;'
Ответ 7
Ни один из приведенных ответов не может найти ошибку в результате переименования или удаления таблицы
но будьте счастливы, у меня есть решение для SQL Server 2017 и более поздних версий:
DECLARE @NumberRecords INT
DECLARE @RowCount INT
DECLARE @Name NVARCHAR(MAX)
DECLARE @Command NVARCHAR(MAX)
DECLARE @Result int
DECLARE @Names TABLE (
[RowId] INT NOT NULL IDENTITY(1, 1),
[Name] NVARCHAR(MAX),
[Type] NVARCHAR(MAX)
)
INSERT INTO @Names
SELECT
QUOTENAME(SCHEMA_NAME([Objects].schema_id)) + '.' + QUOTENAME(OBJECT_NAME([Objects].object_id)) [Name],
type_desc [Type]
FROM sys.objects [Objects]
WHERE type_desc IN ('SQL_STORED_PROCEDURE',
'SQL_TRIGGER',
'SQL_SCALAR_FUNCTION',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'VIEW')
ORDER BY [Name]
SET @RowCount = 1
SET @NumberRecords = (SELECT COUNT(*) FROM @Names)
WHILE (@RowCount <= @NumberRecords)
BEGIN
SELECT @Name = [Name]
FROM @Names
WHERE [RowId] = @RowCount
SET @Command = N'EXEC sp_refreshsqlmodule ''' + @Name + ''''
BEGIN TRY
EXEC @Result = sp_executesql @Command
IF @Result <> 0
BEGIN
RAISERROR('Failed', 16, 1)
END
ELSE
BEGIN
IF (NOT EXISTS (SELECT *
FROM sys.dm_sql_referenced_entities(@Name, 'OBJECT')
WHERE [is_incomplete] = 1))
BEGIN
DELETE
FROM @Names
WHERE [RowId] = @RowCount
END
END
END TRY
BEGIN CATCH
-- Nothing
END CATCH
SET @RowCount = @RowCount + 1
END
SELECT [Name],
[Type]
FROM @Names
Ответ 8
Та же идея, но более универсальная - вы проверяете все определенные пользователем объекты с телами, и это показывает вам ошибку во время компиляции. Это действительно полезно после переименования/удаления объектов/столбцов и т.д.
Просто запустите его после обновления схемы базы данных, чтобы убедиться, что все объекты тела все еще действительны
DECLARE @obj_name AS sysname, @obj_type AS sysname
DECLARE obj_cursor CURSOR FOR
SELECT SCHEMA_NAME(o.schema_id) + '.' + o.name, o.type_desc
FROM sys.objects o
INNER JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE o.is_ms_shipped = 0 AND m.is_schema_bound = 0
ORDER BY o.type_desc, SCHEMA_NAME(o.schema_id), o.name
OPEN obj_cursor
FETCH NEXT FROM obj_cursor INTO @obj_name, @obj_type
WHILE (@@FETCH_STATUS <> -1)
BEGIN
BEGIN TRY
EXEC sp_refreshsqlmodule @obj_name
--PRINT 'Refreshing ''' + @obj_name + ''' completed'
END TRY
BEGIN CATCH
PRINT 'ERROR - ' + @obj_type + ' ''' + @obj_name + ''':' + ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM obj_cursor INTO @obj_name, @obj_type
END
CLOSE obj_cursor
DEALLOCATE obj_cursor
Ответ 9
После того как я внес изменения в таблицу, например переименование столбца, я должен изменить все хранимые процедуры, функции и представления, которые ссылаются на столбец таблицы. Очевидно, я должен вручную изменить их один за другим. Но моя база данных содержит сотни подобных объектов. Поэтому я хотел убедиться, что я изменил все зависимые объекты. Одним из решений является перекомпиляция всех объектов (через скрипт). Но перекомпиляция происходит только для каждого объекта при следующем выполнении. Но я хочу проверить их и получить детали сейчас.
Для этого я могу использовать " sp_refreshsqlmodule " вместо " sp_recompile ". Это обновит каждый объект и выдаст сообщение об ошибке, если он не анализируется правильно. Вот сценарий ниже;
-- table variable to store procedure names
DECLARE @tblObjects TABLE (ObjectID INT IDENTITY(1,1), ObjectName
sysname)
-- get the list of stored procedures, functions and views
INSERT INTO @tblObjects(ObjectName)
SELECT '[' + sc.[name] + '].[' + obj.name + ']'
FROM sys.objects obj
INNER JOIN sys.schemas sc ON sc.schema_id = obj.schema_id
WHERE obj.[type] IN ('P', 'FN', 'V') -- procedures, functions, views
-- counter variables
DECLARE @Count INT, @Total INT
SELECT @Count = 1
SELECT @Total = COUNT(*) FROM @tblObjects
DECLARE @ObjectName sysname
-- start the loop
WHILE @Count <= @Total BEGIN
SELECT @ObjectName = ObjectName
FROM @tblObjects
WHERE ObjectID = @Count
PRINT 'Refreshing... ' + @ObjectName
BEGIN TRY
-- refresh the stored procedure
EXEC sp_refreshsqlmodule @ObjectName
END TRY
BEGIN CATCH
PRINT 'Validation failed for : ' + @ObjectName + ', Error:' +
ERROR_MESSAGE() + CHAR(13)
END CATCH
SET @Count = @Count + 1
END
Если какой-либо объект выдает ошибку, я могу теперь обратиться к нему и вручную исправить проблему с ним.
Ответ 10
Мой подход был немного другим. Я создал скрипт alter для нескольких процессов в SSMS, а затем подождал несколько секунд, чтобы SSMS обработал их, и я получил то, что хотел:
Затем SSMS вправо ограничивает красную точку для любой строки ошибки, которую я могу легко проверить, исправить и позже выполнить тот же сценарий, чтобы обновить его правильными значениями.