Trova oggetti danneggiati in SQL Server

C’è uno strumento che troverà tutti gli oggetti in SQL Server (funzioni, proc, viste) che non possono funzionare perché si riferiscono a oggetti che non esistono?

In realtà sto usando la procedura sys.refreshmodule ora racchiusa in uno script PowerShell con i componenti aggiuntivi di Powershell di SQL Server.

Funziona meglio perché questa pratica e piccola funzione sys elimina le cose CREATE vs ALTER. Alcune altre risposte qui usano anche questo approccio, ma preferisco questo che è avvolto in Powershell e forse alcuni lo troveranno utile.

$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 } } } } 

Potresti essere interessato a controllare i seguenti articoli:

  • Michael J. Swart: trova le dipendenze SQL mancanti
  • eggheadcafe.com: trova le cose rotte

Puoi testare la soluzione di Michael J. Swart come segue:

 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; 

Che restituisce:

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

Le due soluzioni precedenti qui sono interessanti, ma entrambe sono fallite nei miei database di test.

La sceneggiatura originale di Michael J Swart ha prodotto un numero enorme di falsi positivi per me, troppi da attraversare. La soluzione di Rick V. qui era migliore – gli unici falsi positivi che ha fornito erano per i riferimenti cross-database.

C’è un commento sull’articolo di Michael J Swart di RaduSun che fornisce una soluzione che non posso ancora interrompere! Questo è, ottimizzato leggermente per la leggibilità e i miei scopi, ma credito a RaduSun per la logica.

 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 

Il prompt 5 di Red Gate Software ha una funzione Trova oggetti non validi che potrebbe essere utile in questa situazione. Lo strumento passa attraverso gli oggetti di ricerca del database che daranno un errore quando viene eseguito, il che suona esattamente come desideri.

Puoi scaricare gratuitamente una versione di prova di 14 giorni, quindi puoi provare e vedere se è utile.

Paul Stephenson
SQL Prompt Project Manager
Software Red Gate

 /* 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 ti darà il nome di oggetti rotti include Stored Procedure , View , Scalar function , DML trigger , tipo di Table-valued-function

 /* ///////////// ////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 

E la below one ricerca di riferimenti non risolti .. In genere, che viene trattata come warning , è ancora può causare un error qualche volta

 /* ///////////// ///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] 

Grazie a @SQLMonger .. per avermi fornito l'indizio per fare la First query che era il mio requisito reale

Alcuni anni fa ho scritto una sceneggiatura che troverà stored procedure che non verranno compilate tirando il testo del proc e tentando di ricompilarlo con un blocco try / catch. È abbastanza semplice ed efficace trovare almeno procedure che possono essere eliminate. Potresti facilmente espanderlo per le viste.

Si noti che è necessario eseguirlo solo su un ambiente DEV o TEST poiché sta effettivamente tentando di ricompilare le procedure.

 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/

A partire da SQL Server 2008, un metodo molto più semplice è qui:

  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; 

Come accennato nell’articolo di origine (articolo MSDN di Microsoft sulla ricerca di dipendenze mancanti ), il valore “A” NULL in “Oggetto trovato?” colonna indica che l’object non è stato trovato in sys.objects. ”

Esempio di output:

 ╔═══════════════════════════════════════════════╦═══════════════════════╦════════════════════════╦═══════════════════════════════════════╦═══════════════╗ ║ 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 ║ ╚═══════════════════════════════════════════════╩═══════════════════════╩════════════════════════╩═══════════════════════════════════════╩═══════════════╝ 

La soluzione migliore è iniziare a utilizzare uno strumento come Visual Studio Database Edition. Il suo ruolo è gestire uno schema di database. Una delle molte cose che farà è generare un errore quando si tenta di creare il progetto del database e contiene oggetti rotti. Ovviamente farà molto più di questo. Lo strumento è gratuito per qualsiasi utente di Visual Studio Team Suite o Visual Studio Developer Edition.

Nota la query in questo thread trova oggetti mancanti, non quelli non validi.
SQL Server non trova un object di riferimento non valido finché non lo si esegue.

Miglioramento a quella query per gestire oggetti in altri schemi e tipi:

 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 + ']'