How to refresh updatable views in a SQL Server database

How to refresh updatable views in a SQL Server database (PowerShell)

Introduction

This PowerShell script will demonstrate how to refresh updatable views in a SQL Server database.

Scenarios

As many people asked how to refresh updatable views in a SQL Server database, this script will provide some convenience. But it will not update the metadata for the specified non-schema-bound view.

Script

You can use this script in this way:
1. Run Microsoft PowerShell as Administrator

2. Run the script in the form: Path
For example: D:\Syncup\UpdateViewsInOneInstance.ps1

3. Press “Enter” and enter the server name and database name.
Note: server name should be in the format of Server Name\ Instance Name. If it’s a default instance, please enter the server name.

4. Press “Enter” and choose between Windows and SQL authentication. If you choose SQL Server authentication, please enter the correct user name and password.

5. After the script finishes running, we’ll get the following figure:

Here are some code snippets for your reference:

PowerShell

Edit|Remove

if($ServerName -and $DatabaseName)
    {  
        $SQLConnection = NewSQLConnection $ServerName 
        try{        
                #Open database
                $SQLConnection.Open()

                #SQL query statement to get backup information
                $SQLQuery = "
                            DECLARE @i int
                            DECLARE @count int

                            IF OBJECT_ID('ViewTable','U') IS NOT NULL
                            DROP TABLE ViewTable
                            CREATE TABLE ViewTable(ID int IDENTITY(1,1) ,ViewName sysname)
 
                            INSERT INTO ViewTable(ViewName)
                            SELECT TABLE_SCHEMA+'.'+TABLE_NAME FROM information_schema.views v
                            INNER JOIN sys.objects o
                            ON v.TABLE_NAME = o.name
                            AND o.type = 'V'
                            AND OBJECTPROPERTY(o.[object_id], 'IsSchemaBound') <> 1
                            AND OBJECTPROPERTY(o.[object_id], 'IsMsShipped') <> 1

                            SET @i = 1
                            SET @count = (SELECT COUNT(*) FROM ViewTable)
                            WHILE(@i <= @count)
                            BEGIN
                                DECLARE @ViewName sysname
                                SET @ViewName = (SELECT ViewName FROM ViewTable WHERE ID = @i)
                                EXEC sp_refreshview  @ViewName
                                SET @i = @i + 1    
                                PRINT 'Updated ' + @ViewName
                            END
                            SELECT * FROM ViewTable                               
                            "
                            
                #Create SQLDataAdapter object with command text and connection
                $SQLDataSet = New-Object System.Data.DataSet
                $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLQuery,$SQLConnection)
                                       
                $SQLAdapter.Fill($SQLDataSet) | Out-Null
                                 
                $table = $SQLDataSet.Tables
               
               #To show the result
               if($table.Rows.count)
                {                        
                Write-Host "---------------"
                Write-Host "Updated views: "
                Write-Host "---------------" 
                $table|format-list}
               else
               {
                Write-Host "No view was updated!"
               }                       
             }
        catch 
        {
                Write-Error $_
        }
    }

if($ServerName-and $DatabaseName) 
    {   
        $SQLConnection = NewSQLConnection $ServerNametry{         
                #Open database$SQLConnection.Open() 
 
                #SQL query statement to get backup information$SQLQuery = " 
                            DECLARE @i int 
                            DECLARE @count int 
 
                            IF OBJECT_ID('ViewTable','U') IS NOT NULL 
                            DROP TABLE ViewTable 
                            CREATE TABLE ViewTable(ID int IDENTITY(1,1) ,ViewName sysname) 
  
                            INSERT INTO ViewTable(ViewName) 
                            SELECT TABLE_SCHEMA+'.'+TABLE_NAME FROM information_schema.views v 
                            INNER JOIN sys.objects o 
                            ON v.TABLE_NAME = o.name 
                            AND o.type = 'V' 
                            AND OBJECTPROPERTY(o.[object_id], 'IsSchemaBound') <> 1 
                            AND OBJECTPROPERTY(o.[object_id], 'IsMsShipped') <> 1 
 
                            SET @i = 1 
                            SET @count = (SELECT COUNT(*) FROM ViewTable) 
                            WHILE(@i <= @count) 
                            BEGIN 
                                DECLARE @ViewName sysname 
                                SET @ViewName = (SELECT ViewName FROM ViewTable WHERE ID = @i) 
                                EXEC sp_refreshview  @ViewName 
                                SET @i = @i + 1     
                                PRINT 'Updated '+ @ViewName 
                            ENDSELECT*FROM ViewTable                                
                            " 
                             
                #Create SQLDataAdapter object with command text and connection$SQLDataSet = New-Object System.Data.DataSet 
                $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLQuery,$SQLConnection) 
                                        
                $SQLAdapter.Fill($SQLDataSet) |Out-Null$table = $SQLDataSet.Tables 
                
               #To show the resultif($table.Rows.count) 
                {                         
                Write-Host "---------------" 
                Write-Host "Updated views: " 
                Write-Host "---------------"$table|format-list} 
               else 
               { 
                Write-Host "No view was updated!" 
               }                        
             } 
        catch  
        { 
                Write-Error$_ 
        } 
    }

Prerequisites

PowerShell 2.0 or higher