Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register 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