Find Invalid Objects in SQL Server

  • Comments posted to this topic are about the item Find Invalid Objects in SQL Server

  • Link is going back to a topic written in 2012 which is an  entirely different.

    =======================================================================

  • I'd think it is better to really recompile all the procedures / triggers / views / functions etc. instead to hope, that the sys.dependencies-table is always 100% correct.

    DECLARE @full_name sysname

    DECLARE curModule CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT OBJECT_SCHEMA_NAME(sm.object_id) + '.' + OBJECT_NAME(sm.object_id)
    FROM sys.sql_modules AS sm
    ORDER BY 1
    ;
    OPEN curModule;

    WHILE 1 = 1
    BEGIN
    FETCH NEXT FROM curModule INTO @full_name
    IF @@fetch_status <> 0 BREAK;
    PRINT 'Compiling ' + @full_name
    EXEC sys.sp_recompile @objname = @full_name
    END;

    CLOSE curModule;
    DEALLOCATE curModule;

    will do the trick (feel free to check for the return code etc for handling invalid stuff).

    PS: if you are using SELECT * in particularly in your views (something you should really avoid), you need to recompile the view always after changing the base tables (otherwise you could end with wrong content in the columns, e.g. if you dropped an unused column in the middle or changed a computed column (=DROP and ADD with new formula)

    God is real, unless declared integer.

  • Thomas Franz wrote:

    I'd think it is better to really recompile all the procedures / triggers / views / functions etc. instead to hope, that the sys.dependencies-table is always 100% correct.

    DECLARE @full_name sysname

    DECLARE curModule CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT OBJECT_SCHEMA_NAME(sm.object_id) + '.' + OBJECT_NAME(sm.object_id)
    FROM sys.sql_modules AS sm
    ORDER BY 1
    ;
    OPEN curModule;

    WHILE 1 = 1
    BEGIN
    FETCH NEXT FROM curModule INTO @full_name
    IF @@fetch_status <> 0 BREAK;
    PRINT 'Compiling ' + @full_name
    EXEC sys.sp_recompile @objname = @full_name
    END;

    CLOSE curModule;
    DEALLOCATE curModule;

    will do the trick (feel free to check for the return code etc for handling invalid stuff).

    PS: if you are using SELECT * in particularly in your views (something you should really avoid), you need to recompile the view always after changing the base tables (otherwise you could end with wrong content in the columns, e.g. if you dropped an unused column in the middle or changed a computed column (=DROP and ADD with new formula)

     

    The big downside using te sp_recompile , is that you also invalidate cached plans ( of correct objects ), so they will need to be compiled and that may influence your ongoing applications.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • This is useful for me since it gives me a way of finding invalid objects without recompiling.  I was using a script that recompiled the objects to find the broken dependencies mentioned previously. Your script does list some invalid objects which are not actually invalid however in triggers: Inserted, Deleted, and dbo.sp_send_dbmail. Also the first time I ran it failed with a PK error so you should consider removing that or making the PK column an identity?  It needs more rigorous testing since it's identifying other objects which are not invalid; in my case I have a view created in schema "myschema" on a table in schema "dbo" that joins another view in schema "dbo" where your script says the joined/referenced dbo schema view is invalid.

  • This version also handles most cross database queries:

     

    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb.dbo.#invalid_db_objects') IS NOT NULL
    BEGIN
    DROP TABLE #invalid_db_objects;
    END;

    CREATE TABLE #invalid_db_objects
    (DbName SYSNAME NOT NULL
    , invalid_object_id INT
    , invalid_obj_name NVARCHAR(1000)
    , referenced_object NVARCHAR(1000) NOT NULL
    , invalid_obj_type CHAR(2) NOT NULL
    , Referenced_DbId INT
    , referenced_database_name NVARCHAR(1000) NOT NULL
    , referenced_server_name NVARCHAR(1000) NOT NULL
    );

    INSERT INTO #invalid_db_objects
    SELECT DB_NAME() AS DbName
    , RefSel.referencing_id
    , QUOTENAME(SCHEMA_NAME(all_object.[schema_id]))
    + '.'
    + QUOTENAME(all_object.name) AS obj_name
    , RefSel.referenced_object
    , all_object.[type]
    , RefSel.Referenced_DbId
    , RefSel.referenced_database_name
    , RefSel.referenced_server_name
    FROM (
    SELECT sed.referencing_id
    , COALESCE(sed.referenced_schema_name
    + '.', '')
    + sed.referenced_entity_name as referenced_object
    , DB_ID(ISNULL(referenced_database_name, DB_NAME())) AS Referenced_DbId
    , COALESCE(referenced_server_name, CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')), CONVERT(SYSNAME, SERVERPROPERTY('ServerName'))) AS referenced_server_name
    , ISNULL(referenced_database_name, DB_NAME()) AS referenced_database_name
    FROM sys.sql_expression_dependencies AS sed
    WHERE sed.is_ambiguous = 0
    AND sed.referenced_id IS NULL
    ) AS RefSel
    JOIN sys.objects AS all_object
    ON RefSel.referencing_id = all_object.[object_id];

    SELECT DbName
    , invalid_object_id
    , invalid_obj_name
    , referenced_object
    , invalid_obj_type
    , Referenced_DbId
    , referenced_database_name
    , referenced_server_name
    FROM #invalid_db_objects
    WHERE OBJECT_ID(QUOTENAME(referenced_database_name) + '.' + referenced_object) IS NULL
    ORDER BY DbName
    , invalid_obj_name
    , referenced_object;

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • The primary key for #invalid_db_objects is on the invalid_object_id column, but if that object references multiple missing objects, then the insert will fail due to a duplicate key error. I've made the invalid_object_id column NOT NULL, taken the primary key definition out of the table creation statement, and created it using 2 columns as follows:

    ALTER TABLE #invalid_db_objects ADD CONSTRAINT PK_invalid_db_objects_invalid_object_id_custom_error_message PRIMARY KEY CLUSTERED (invalid_object_id, custom_error_message)

    This allows for such multiple references.

  • @SQL Simon, the #invalid_db_objects table does not have a column custom_error_message in it.

    Sorry, yes, the original post contains a custom_error_message column. I was trying to incorporate the code in a comment.

  • Very cool and useful. Thank-you for your efforts in writing this up.

    I also ran into some issues with the Primary key, and changed it to

    constraint pk_invalid primary key (invalid_object_id, custom_error_message) with (ignore_dup_key = on)

    We also have triggers that make extensive use of the "deleted" and "inserted" virtual tables, so I excluded them from my results

    SELECT invalid_obj_name [Invalid OBJECT NAME] , custom_error_message [Error Message], invalid_obj_type [Object Type] FROM #invalid_db_objects where custom_error_message not in ('Invalid object name ''inserted''', 'Invalid object name ''deleted''')
  • @Toby Harman, I am not sure what meaning IGNORE_DUP_KEY would have for a PRIMARY key which must be unique. See also, https://stackoverflow.com/a/2594283/447901

  • It allows you to enter duplicate values and then drops them semi-silently.

  • This was removed by the editor as SPAM

  • I would switch the temp table to be

    CREATE TABLE #invalid_db_objects 
    (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    invalid_object_id INT NOT NULL,
    invalid_obj_name NVARCHAR(1000) NOT NULL,
    custom_error_message NVARCHAR(3000) NOT NULL,
    invalid_obj_type CHAR(2) NOT NULL
    );

    In case an object has more than one error, as mine did.

    Ozzie

    • This reply was modified 1 year, 2 months ago by  SQLOzzie.
  • Beyond what others have stated, this will have many false positives.  Triggers that reference INSERTED and DELETED will show as invalid.  Excluding these is pretty simple.  However, update and delete statements referencing an alias will show as being invalid.  In the example below, t1 will show as being invalid.

    UPDATE t1
    SET Col1 = t2.value1
    FROM dbo.table1 t1
    JOIN dbo.table2 t2 ON t1.id = t2.id

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply