Поиск сломанных объектов в SQL Server

Есть ли инструмент, который найдет все объекты в SQL Server (функции, procs, views), которые не могут работать, потому что они ссылаются на объекты, которые не существуют?

10 ответов

На самом деле я использую процедуру sys.refreshmodule, которая теперь завернута в powershell script с добавлением SQL Server Powershell.

Это работает лучше, потому что эта удобная небольшая функция sys избавляется от материала CREATE vs ALTER. Некоторые другие ответы здесь также используют этот подход, но я предпочитаю этот, который завернут в Powershell, и, возможно, некоторые из них посчитают это полезным.

$server = "YourDBServer"
cls
Import-Module "sqlps" -DisableNameChecking
$databases = Invoke-Sqlcmd -Query "select name from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb')" -ServerInstance $server
foreach ($db in $databases) {
 $dbName = $db.name
 $procedures = Invoke-Sqlcmd -Query "select SCHEMA_NAME(schema_id) as [schema], name from $dbName.sys.procedures" -ServerInstance $server
 foreach ($proc in $procedures) {
 if ($schema) {
 $shortName = $proc.schema + "." + $proc.name
 $procName = $db.name + "." + $shortName
 try {
 $result = Invoke-Sqlcmd -Database $dbName -Query "sys.sp_refreshsqlmodule '$shortName'" -ServerInstance $server -ErrorAction Stop
 Write-Host "SUCCESS|$procName"
 }
 catch {
 $msg = $_.Exception.Message.Replace([Environment]::NewLine, ",")
 Write-Host "FAILED|$procName|$msg" -ForegroundColor Yellow
 }
 }
 }
}


Вам может быть интересно узнать следующие статьи:

Вы можете проверить решение Майкла Дж. Сварта следующим образом:

CREATE PROCEDURE proc_bad AS
 SELECT col FROM nonexisting_table
GO
SELECT
 OBJECT_NAME(referencing_id) AS [this sproc or VIEW...],
 referenced_entity_name AS [... depends ON this missing entity name]
FROM 
 sys.sql_expression_dependencies
WHERE 
 is_ambiguous = 0
 AND OBJECT_ID(referenced_entity_name) IS NULL
ORDER BY 
 OBJECT_NAME(referencing_id), referenced_entity_name;

Что возвращает:

+------------------------+------------------------------------------+
| this sproc or VIEW... | ... depends ON this missing entity name |
|------------------------+------------------------------------------|
| proc_bad | nonexisting_table |
+------------------------+------------------------------------------+


Программное обеспечение Red Gate SQL Prompt 5 имеет функцию Найти недопустимые объекты, которая может быть полезна в этой ситуации. Инструмент проходит через базу данных, обнаруживая объекты, которые будут выдавать ошибку при выполнении, что звучит именно так, как вы хотите.

Вы можете бесплатно скачать 14-дневную пробную версию, чтобы вы могли попробовать и посмотреть, помогает ли она.

Пол Стивенсон SQL Prompt Project Manager Программное обеспечение Red Gate


Два предыдущих решения здесь интересны, но оба из них были неудачными в тестовых базах данных.

Оригинальный Michael J Swart script произвел огромное количество ложных срабатываний для меня, слишком много, чтобы пробраться. Решение Рика V. здесь было лучше - единственные ложные срабатывания, которые он давал, были для ссылок между базами данных.

Там есть комментарий к статье Михаэля J Swart от RaduSun, которая дает решение, которое я пока не могу сломать! Это он, мягко изменился для удобства чтения и моих целей, но кредит для RaduSun для логики.

SELECT 
 QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' 
 + QuoteName(OBJECT_NAME(referencing_id)) AS ProblemObject,
 o.type_desc,
 ISNULL(QuoteName(referenced_server_name) + '.', '')
 + ISNULL(QuoteName(referenced_database_name) + '.', '')
 + ISNULL(QuoteName(referenced_schema_name) + '.', '')
 + QuoteName(referenced_entity_name) AS MissingReferencedObject
FROM
 sys.sql_expression_dependencies sed
 LEFT JOIN sys.objects o
 ON sed.referencing_id=o.object_id
WHERE
 (is_ambiguous = 0)
 AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '')
 + ISNULL(QuoteName(referenced_database_name) + '.', '')
 + ISNULL(QuoteName(referenced_schema_name) + '.', '')
 + QuoteName(referenced_entity_name)) IS NULL)
ORDER BY
 ProblemObject,
 MissingReferencedObject


/*
modified version of script from http://michaeljswart.com/2009/12/find-missing-sql-dependencies/
Added columns for object types & generated refresh module command...
filter out user-define types: http://stackoverflow.com/questions/2330521/find-broken-objects-in-sql-server

*/

SELECT TOP (100) PERCENT
 QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS [this Object...],
 o.type_desc,
 ISNULL(QuoteName(referenced_server_name) + '.', '')
 + ISNULL(QuoteName(referenced_database_name) + '.', '')
 + ISNULL(QuoteName(referenced_schema_name) + '.', '')
 + QuoteName(referenced_entity_name) AS [... depends ON this missing entity name]
 ,sed.referenced_class_desc
 ,case when o.type_desc in( 'SQL_STORED_PROCEDURE' ,'SQL_SCALAR_FUNCTION' ,'SQL_TRIGGER' ,'VIEW')
 then 'EXEC sys.sp_refreshsqlmodule ''' + QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) + ''';'
 else null
 end as [Refresh SQL Module command]
FROM sys.sql_expression_dependencies as sed
LEFT JOIN sys.objects o
 ON sed.referencing_id=o.object_id
WHERE (is_ambiguous = 0)
AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '')
 + ISNULL(QuoteName(referenced_database_name) + '.', '')
 + ISNULL(QuoteName(referenced_schema_name) + '.', '')
 + QuoteName(referenced_entity_name)) IS NULL)
AND NOT EXISTS
 (SELECT * 
 FROM sys.types 
 WHERE types.name = referenced_entity_name 
 AND types.schema_id = ISNULL(SCHEMA_ID(referenced_schema_name), SCHEMA_ID('dbo'))
 )
ORDER BY [this Object...],
[... depends ON this missing entity name]


First query даст вам сломанные имена объектов: Stored Procedure, View, Scalar function, DML trigger, Table-valued-function type

/*
/////////////
////ERROR////
/////////////
All error will be listed if object is broken
*/
DECLARE @AllObjectName TABLE (
 OrdinalNo INT IDENTITY
 ,ObjectName NVARCHAR(MAX)
 ,ObjectType NVARCHAR(MAX)
 ,ErrorMessage NVARCHAR(MAX)
 )
INSERT INTO @AllObjectName (
 ObjectName
 ,ObjectType
 )
SELECT '[' + SCHEMA_NAME(schema_id) + '].[' + NAME + ']' ObjectName
 ,CASE [TYPE]
 WHEN 'P'
 THEN 'Stored Procedure'
 WHEN 'V'
 THEN 'View'
 WHEN 'FN'
 THEN 'Scalar function'
 WHEN 'TR'
 THEN 'DML trigger'
 WHEN 'TF'
 THEN 'Table-valued-function'
 ELSE 'Unknown Type'
 END
FROM sys.objects
WHERE [TYPE] IN (
 'P'
 ,'V'
 ,'FN'
 ,'TR'
 ,'TF'
 )
ORDER BY NAME
DECLARE @i INT = 1
DECLARE @RowCount INT = (
 SELECT count(1)
 FROM @AllObjectName
 )
DECLARE @ObjectName VARCHAR(MAX)
WHILE @i <= @RowCount
BEGIN
 BEGIN TRY
 SET @ObjectName = (
 SELECT ObjectName
 FROM @AllObjectName
 WHERE OrdinalNo = @i
 )
 EXEC sys.sp_refreshsqlmodule @ObjectName
 END TRY
 BEGIN CATCH
 DECLARE @message VARCHAR(4000)
 ,@xstate INT;
 SELECT @message = ERROR_MESSAGE()
 ,@xstate = XACT_STATE();
 IF @xstate = - 1
 ROLLBACK;
 UPDATE @AllObjectName
 SET ErrorMessage = @message
 WHERE OrdinalNo = @i
 END CATCH
 SET @i = @i + 1
END
SELECT ObjectName
 ,ObjectType
 ,ErrorMessage
FROM @AllObjectName
WHERE ErrorMessage IS NOT NULL

И below one поиск неразрешенных ссылок.. Обычно, который обрабатывается как warning, все равно может возникнуть error sometime

/*
/////////////
///Warning///
/////////////
Here all warning will come if object reference is not stated properly
*/
SELECT TOP (100) PERCENT QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS [this Object...]
 ,o.type_desc
 ,ISNULL(QuoteName(referenced_server_name) + '.', '') + ISNULL(QuoteName(referenced_database_name) + '.', '') + ISNULL(QuoteName(referenced_schema_name) + '.', '') + QuoteName(referenced_entity_name) AS [... depends ON this missing entity name]
 ,sed.referenced_class_desc
FROM sys.sql_expression_dependencies AS sed
LEFT JOIN sys.objects o ON sed.referencing_id = o.object_id
WHERE (is_ambiguous = 0)
 AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '') + ISNULL(QuoteName(referenced_database_name) + '.', '') + ISNULL(QuoteName(referenced_schema_name) + '.', '') + QuoteName(referenced_entity_name)) IS NULL)
 AND NOT EXISTS (
 SELECT *
 FROM sys.types
 WHERE types.NAME = referenced_entity_name
 AND types.schema_id = ISNULL(SCHEMA_ID(referenced_schema_name), SCHEMA_ID('dbo'))
 )
ORDER BY [this Object...]
 ,[... depends ON this missing entity name]

Спасибо @SQLMonger.. за предоставленную мне подсказку, чтобы сделать First query, который был моим фактическим требованием


В SQL Server 2008 гораздо более простой способ:

SELECT OBJECT_NAME(referencing_id) AS 'object making reference' ,
 referenced_class_desc ,
 referenced_schema_name ,
 referenced_entity_name AS 'object name referenced' ,
 ( SELECT object_id
 FROM sys.objects
 WHERE name = [referenced_entity_name]
 ) AS 'Object Found?'
FROM sys.sql_expression_dependencies e
 LEFT JOIN sys.tables t ON e.referenced_entity_name = t.name;

Как упоминалось в исходной статье (Microsoft MSDN Article on Finding Missing Dependencies), "Значение" NULL "в" Объекте найдено? "column указывает, что объект не найден в sys.objects."

Пример вывода:

╔═══════════════════════════════════════════════╦═══════════════════════╦════════════════════════╦═══════════════════════════════════════╦═══════════════╗
║ object making reference ║ referenced_class_desc ║ referenced_schema_name ║ object name referenced ║ Object Found? ║
╠═══════════════════════════════════════════════╬═══════════════════════╬════════════════════════╬═══════════════════════════════════════╬═══════════════╣
║ usvConversationsWithoutServerNotices ║ OBJECT_OR_COLUMN ║ dbo ║ ConversationLinesWithID ║ NULL ║
║ usvFormattedConversationLines_WithSpeakerName ║ OBJECT_OR_COLUMN ║ dbo ║ ConversationLinesWithID ║ NULL ║
║ usvFormattedConversationLines_WithSpeakerName ║ OBJECT_OR_COLUMN ║ dbo ║ FormattedConversationLines_Cached ║ NULL ║
║ udpCheckForDuplicates ║ OBJECT_OR_COLUMN ║ dbo ║ FormattedConversationLines_WithChatID ║ NULL ║
║ usvFormattedConversationsCombined ║ OBJECT_OR_COLUMN ║ dbo ║ GROUP_CONCAT_D ║ 178099675 ║
║ usvSequenceCrossValidationSetStudents ║ OBJECT_OR_COLUMN ║ dbo ║ usvSequenceCrossValidationSet ║ 1406628054 ║
╚═══════════════════════════════════════════════╩═══════════════════════╩════════════════════════╩═══════════════════════════════════════╩═══════════════╝


Несколько лет назад я написал script, который найдет хранимые процедуры, которые не будут скомпилированы, вытащив текст proc и попытавшись перекомпилировать его с помощью блока try/catch. Это довольно просто и эффективно, чтобы найти как минимум процедуры, которые можно отбросить. Вы можете легко расширить его для просмотра.

Обратите внимание, что вы должны запускать его только с помощью среды DEV или TEST, поскольку она фактически пытается перекомпилировать процедуры.

SET NOCOUNT ON
DECLARE @ProcedureName VARCHAR(2048)
DECLARE @ProcedureBody VARCHAR(MAX)
DECLARE @RoutineName varchar(500)
DECLARE procCursor CURSOR STATIC FORWARD_ONLY READ_ONLY
 FOR
 SELECT
 --TOP 1
 SCHEMA_NAME(schema_id) + '.' + NAME AS ProcedureName,
 OBJECT_DEFINITION(o.[object_id]) AS ProcedureBody
 FROM sys.objects AS o
 WHERE o.[type] = 'P'
 ORDER BY o.[name]
OPEN procCursor
FETCH NEXT FROM procCursor INTO @ProcedureName, @ProcedureBody
WHILE @@FETCH_STATUS = 0
BEGIN
 -- Might have to play with this logic if you don't have discipline in your create statements
 SET @ProcedureBody = REPLACE(@ProcedureBody, 'CREATE PROCEDURE', 'ALTER PROCEDURE')
 BEGIN TRY
 EXECUTE(@ProcedureBody)
 PRINT @ProcedureName + ' -- Succeeded'
 END TRY
 BEGIN CATCH
 PRINT @ProcedureName + ' -- Failed: ' + ERROR_MESSAGE()
 END CATCH
 FETCH NEXT FROM procCursor INTO @ProcedureName, @ProcedureBody
END
CLOSE procCursor
DEALLOCATE procCursor

https://brettwgreen.wordpress.com/2012/12/04/find-stored-procedures-that-wont-compile/


Обратите внимание, что запрос в этом потоке находит отсутствующие объекты, а не недопустимые. SQL Server не обнаруживает, что ссылочный объект недействителен, пока вы его не выполнили.

Улучшение этого запроса для обработки объектов в других схемах, а также типах:

SELECT
 '[' + OBJECT_SCHEMA_NAME(referencing_id) + '].[' + OBJECT_NAME(referencing_id) + ']' 
 AS [this sproc, UDF or VIEW...],
 isnull('[' + referenced_schema_name + '].', '') + '[' + referenced_entity_name + ']' 
 AS [... depends ON this missing entity name]
FROM 
 sys.sql_expression_dependencies
WHERE 
 is_ambiguous = 0 AND 
 (
 (
 [referenced_class_desc] = 'TYPE' and 
 TYPE_ID(
 isnull('[' + referenced_schema_name + '].', '') + 
 '[' + referenced_entity_name + ']'
 ) IS NULL
 ) or
 ( 
 [referenced_class_desc] <> 'TYPE' and 
 OBJECT_ID(
 isnull('[' + referenced_schema_name + '].', '') + 
 '[' + referenced_entity_name + ']'
 ) IS NULL
 )
 )
ORDER BY 
 '[' + OBJECT_SCHEMA_NAME(referencing_id) + '].[' + OBJECT_NAME(referencing_id) + ']',
 isnull('[' + referenced_schema_name + '].', '') + '[' + referenced_entity_name + ']'


Лучше всего начать использовать такой инструмент, как Visual Studio Database Edition. Это роль управления схемой базы данных. Одна из многих вещей, которые он сделает, - это выбросить ошибку при попытке создать проект базы данных и содержать сломанные объекты. Конечно, это будет намного больше. Этот инструмент является бесплатным для любого пользователя Visual Studio Team Suite или Visual Studio Developer Edition.

licensed under cc by-sa 3.0 with attribution.