Submit a ticketCall us

Have You Auto Renewed? If not, you're missing out.
The SolarWinds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more. Experience all of this with the convenience of Auto Renewal, and never worry about missing any of these great benefits. Learn More.

Home > Success Center > Network Performance Monitor (NPM) > SQL - Drop multiple views in Orion database

SQL - Drop multiple views in Orion database

Table of contents

Updated May 5th, 2016

Overview

This article provides information on how to drop multiple (i.e. all) views in the database which may be useful while upgrading Orion and facing issues with some views not being able to be altered or recreated.

Environment

  • Orion Platform any version

Steps

Below steps will allow you to create set of queries which will be executed, resulting in dropping specified views (in example below: views which names starts with NPM_):

  1. Make sure to backup database although dropping views has no impact on stored data
  2. Open SQL Server Management Studio (SSMS)
  3. Use below query to drop all views
    use YOUR-DB-NAME /* put your database name here */
    GO
    DECLARE @sql VARCHAR(MAX) = ''
    DECLARE @CRLF VARCHAR(2) = CHAR(13) + CHAR(10)
    SELECT @sql = @sql + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @CRLF
    FROM sys.views v
    WHERE v.name LIKE 'NPM_%'
    EXEC(@sql);

    NOTE:
    You can use your own criteria to specify views name (pattern) or skip the WHERE clause to drop all views
     
  4. Run Configuration Wizard selecting option Database otherwise Orion will not work.

 

You can also preview the final command, without execution. To do so simply replace

EXEC(@sql);

with

PRINT(@sql);

 

 

Last modified

Tags

Classifications

Public