Post reply

Find objects referencing tables that don't exist

  • October 30, 2017 at 11:54 pm

    #329399

    HI

    We have a SQL server 2016 with old views and old procs, and they are referencing tables that don't exist anymore - the tables have been dropped after the view/proc were created.
    I need to do a cleanup of these view and procs.
    Can anyone please help with a script to find these specific view and procs referencing non-existing tables?

    Thanks

  • October 31, 2017 at 1:00 am

    #1965894

    Casper101 - Monday, October 30, 2017 11:54 PM

    HI

    We have a SQL server 2016 with old views and old procs, and they are referencing tables that don't exist anymore - the tables have been dropped after the view/proc were created.
    I need to do a cleanup of these view and procs.
    Can anyone please help with a script to find these specific view and procs referencing non-existing tables?

    Thanks

    Use sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities
    😎

    -- Referenced entities
    SELECT * FROM sys.dm_sql_referenced_entities('[SCHEMA_NAME].[OBJECT_NAME]','OBJECT')

    -- Referencing entities
    SELECT * FROM sys.dm_sql_referencing_entities('[SCHEMA_NAME].[OBJECT_NAME]','OBJECT')

Viewing 2 posts - 1 through 1 (of 1 total)

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