Find broken objects in SQL Server

2020-01-29 04:28发布

站内文章 / SQL-Server
38 0 0

问题:

Is there a tool that will find all objects in SQL Server (functions, procs, views) that cannot possibly work because they refer to objects that don't exist?

回答1:

I'm actually using sys.refreshmodule procedure now wrapped in a powershell script with the SQL Server Powershell add ins.

This works better because this handy little sys function gets rid of the CREATE vs ALTER stuff. Some other answers here use this approach as well, but I prefer this one that's wrapped in Powershell and maybe some will find it useful.

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


回答2:

You may be interested in checking out the following articles:

  • Michael J. Swart: Find Missing SQL Dependencies
  • eggheadcafe.com: Find broken stuff

You can test Michael J. Swart's solution as follows:

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;

Which returns:

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


回答3:

The two previous solutions here are interesting, but both failed on my test databases.

The original Michael J Swart script produced a huge number of false positives for me, far too many to wade through. Rick V.'s solution here was better - the only false positives it gave were for cross-database references.

There's a comment on the Michael J Swart article by RaduSun which gives a solution that I can't yet break though! This is it, tweaked mildly for readability and my purposes, but credit to RaduSun for the logic.

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


回答4:

First query will give you broken objects name includes 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

And the below one search for unresolved references .. Generally which treated as warning, is still may cause 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]

Thanks @SQLMonger .. for providing me the clue to make the First query which was my actual requirement



回答5:

Red Gate Software's SQL Prompt 5 has a Find Invalid Objects feature that might be useful in this situation. The tool goes through the database finding objects that will give an error when executed, which sounds exactly what you want.

You can download a 14-day trial for free, so you can give it a try and see if it helps.

Paul Stephenson
SQL Prompt Project Manager
Red Gate Software



回答6:

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


回答7:

As of SQL Server 2008, a much simpler method is here:

 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;

As mentioned in the source article (Microsoft MSDN Article on Finding Missing Dependencies), "A 'NULL' value in the 'Object Found?' column indicates the object was not found in sys.objects."

Example 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    ║
╚═══════════════════════════════════════════════╩═══════════════════════╩════════════════════════╩═══════════════════════════════════════╩═══════════════╝


回答8:

I wrote a script some years ago that will find Stored Procedures that won't compile by pulling the text of the proc and attempting to recompile it with a try/catch block. It's pretty simple and effective at finding at least procedures that can be dropped. You could easily expand it for views.

Note that you should only run this against a DEV or TEST environment since it's actually attempting to recompile the procedures.

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/



回答9:

Your best bet is to start using a tool like Visual Studio Database Edition. It's role is to manage a database schema. One of the many things it will do is to throw an error when you attempt to build the database project and it contains broken objects. It will of course do much more than this. The tool is free to any user of Visual Studio Team Suite or Visual Studio Developer Edition.



回答10:

Note the query in this thread finds missing objects, not invalid ones. 
SQL Server doesn't find a referencing object is invalid until you execute it.

Enhancement to that query to handle objects in other schemas as well as types:

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


回答11:

 create table #BrokenObjects (Name nvarchar(500), Error nvarchar(max))
 select * into #objects from(
 select name from sys.views
 union select name from sys.procedures
 union select name from sys.tables
 )x
 declare @name nvarchar(500),@err nvarchar(max)
 while exists(select top 1 * from #objects)
 begin
 select top 1 @name = name from #objects
 begin try
 EXEC sys.sp_refreshsqlmodule @name
 end try
 begin catch
 select @err = ERROR_MESSAGE()
 insert into #BrokenObjects (name,error) values (@name,@err)
 end catch
 delete from #objects
 where name = @name
 end
 drop table #objects
 select * from #BrokenObjects
 where Error not like 'Could not find object % or you do not have permission.'

 drop table #BrokenObjects


标签： sql-server sql-server-2008
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~