none
Find Orphaned views

    Question

  • Hi All,

    Is there a way to find Orphaned views? I've a lot of views whose underlying tables or columns have been either deleted or renamed etc. The creator of the views didn't use SCHEMABINDING.

    Thanks!


    GBM
    Monday, September 20, 2010 2:59 PM

Answers

  • You could run sp_refreshview for each view in your database. A quick way to get the scripts would be something like:

    select 'EXEC sp_refreshview ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''''
    from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW'
    

    every day is a school day
    • Marked as answer by GBaksh Monday, September 20, 2010 5:37 PM
    Monday, September 20, 2010 3:04 PM
    Moderator

All replies

  • You could run sp_refreshview for each view in your database. A quick way to get the scripts would be something like:

    select 'EXEC sp_refreshview ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''''
    from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW'
    

    every day is a school day
    • Marked as answer by GBaksh Monday, September 20, 2010 5:37 PM
    Monday, September 20, 2010 3:04 PM
    Moderator
  • Hi Richbrownesq,

    Thanks for your script. I didn't know about sp_refreshview. I instead ended up using the following:

    SELECT

     

    'DROP VIEW [' + v.table_schema + '].[' + v.table_name + ']'

    FROM

     

    INFORMATION_SCHEMA.VIEWS v

     

    LEFT JOIN INFORMATION_SCHEMA.VIEW_TABLE_USAGE vtu

     

    ON v.table_catalog = vtu.table_catalog

     

    AND v.table_name = vtu.view_name

    WHERE

    vtu

    .view_name IS NULL


    GBM
    Monday, September 20, 2010 5:36 PM