Submit a ticketCall us

AnnouncementsAre You “Flying Blind?”

When it comes to your complex IT infrastructure, you want to ensure you have a good grasp of what’s going on to avoid any fire drills that result from guesswork. Read our white paper to learn how proactively monitoring your IT environment can help your organization while giving you peace of mind.

Get your free white paper.

Home > Success Center > Orion Platform > Orion - Knowledgebase Articles > 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