Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

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
10:41, 18 Jan 2017

Tags

Classifications

Public