How to generate script to REFRESH all views in a SQL Server Database

Scenario: How to generate script to REFRESH all views in a SQL Server Database

Let's say you have made changes to data types of few of the tables and you had views created on those tables. You need to refresh the views definition after that. The below script can be used to generate script to refresh views.

If you have single view, you can use sp_refreshview 'ViewName' to refresh view definition.

To generate the script for all views in a database, you can run below statement.


SELECT 'sp_refreshview  ''' 
       + Schema_name(schema_id) + '.' + NAME + '''' 
       + Char(13) + Char(10) + ' GO' AS RefreshViewQuery 
FROM   sys.views 



Click on icon "Results to Text" or press Ctrl+T and then copy the script to run for all views. You can always filter the list in your above select query if you would only want to refresh the views related to one schema or as per view names etc.


How to check when the view definition was refreshed in SQL Server

Scenario: How to check when last time view definition was refreshed or changed

We know that if the data type changes for columns for objects which are used in the view, we have to refresh the view definition by using sp_refreshview to update meta data for columns used in the view.

Sometime we need to know what was the last time the view definition was altered or refreshed.
You can use system view to get that information.


Select * from sys.views


Notice that if the view is created and never been altered or definition never been refreshed then created_date=modify_date.

Once you will refresh view definition or alter the view, then modify date will be the date time when the view as altered or definition was refreshed.

How to drop Stored Procedure from All or Multiple databases in SQL Server

Scenario: How to drop Stored Procedure from multiple or all Databases in SQL Server

In my last post, I wrote How to create a Stored Procedure in all the databases or multiple databases in SQL Server. You can Click here to see that post.

In this post we are going to write the script how to drop the Stored Procedure in multiple or all SQL Server databases. You can filter the list of Databases on which you like to run the script by changing the where clause "Select name from sys.databases where *********"

I have cursor to loop through the databases. You need to provide the Schema Name and Stored Procedure that you would like to drop from multiple or all the databases in SQL Server.


--Provide the Stored Procedure Name and Schema Name that you would like to drop from multiple or all databases
    Declare @SP_Schema VARCHAR(128)
    DECLARE @SP_Name VARCHAR(128)

    --Provide Schema Name and Stored Procedure you would like to drop 
    SET @SP_Schema='dbo'
    SET @SP_Name='SP_Test'


    DECLARE @DDL VARCHAR(MAX)
    SET @DDL='IF (EXISTS (Select * from sys.objects
where name='''''+@SP_Name+'''''
and schema_id=schema_id('''''+@SP_Schema+''''')  
and type=''''P''''
and type_desc=''''SQL_STORED_PROCEDURE''''))
BEGIN
Drop Procedure '+@SP_Schema+'.'+@SP_Name+'
END'

Print @DDL
    DECLARE @DatabaseName AS VARCHAR(500)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run DDL statement to drop Stored Procedure
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR

How to Create Same Stored Procedure on All Databases in SQL Server

Scenario:How to Create Same Stored Procedure on All Databases or multiple Databases in SQL Server 

We often face this situation where we need to create a same Stored Procedure in multiple databases. This script can be really usefully when you are working as SQL Server DBA and you have to deploy same Stored Procedure to all databases or multiple databases Or you are working as developer and your team asked you to prepare script which will create the Stored Procedure in all databases in SQL Server.

I used cursor in below script to loop through the databases. You can always change your Where clause to get the list of databases on which you would like to create Stored Procedure. 

It is always great idea to test your script in DEV, QA and UAT environment before deploying/ running on Production environment.

--Provide the DDL Statment that you would like to run for Create Stored Procedure in Each Databsae in SQL Server
-- Notice that if you have string in your SP, then you have to have 4 single quote around it.
    DECLARE @DDL VARCHAR(MAX)
    SET @DDL=    
    'Create Procedure dbo.SP_Test
    AS
    BEGIN
    --My all statements in SP
    Select 1
    Print ''''Test''''
    END'
    DECLARE @DatabaseName AS VARCHAR(500)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(128);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run DDL statement to create Stored Procedure
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR

How to drop View from Multiple or All Databases in SQL Server

Scenario: How to drop View from multiple or all SQL Server Databases

You are working as SQL Server DBA or SQL Server developer, you need to come up with scripts to drop the view from many databases in one of your SQL Server Instance. 

Here is the script that can help to drop the view from multiple databases. I have used the cursor to loop through the databases. You can always modify your select query to choose required databases on which you would like to run the script. You have to change the schema name and view name and in drop view statement, the name of view.

--Provide the DDL Statment that you would like to run to drop the View if exists in Database
  --Change the schema and View name in script as per your requirement
    DECLARE @DDL VARCHAR(MAX)
    SET @DDL='IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = ''''dbo'''' -- change to your Schema
                 AND  TABLE_NAME = ''''MyTestView'''' --Change to your View Name
                 and Table_Type=''''View''''))
BEGIN
Drop view dbo.MyTestview
END'
    DECLARE @DatabaseName AS VARCHAR(500)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run DDL statement to drop View
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR

How to create View in all the databases in SQL Server

Scenario: How to create a view in all SQL Server Databases

You are working as SQL Server DBA or SQL Server developer and you need to create a view with same definition in multiple databases of all databases on SQL Server Instance. You can use below script to create view in multiple databases. I used cursor to loop through the list of databases. You can always filter (choose) the databases in select * from sys.databases query to create view in required databases.


--Provide the DDL Statment that you would like to run for Create View in Each Databsae in SQL Server
    DECLARE @DDL VARCHAR(MAX)
    SET @DDL=    
    'Create View dbo.MyTestView
    AS 
    Select * from dbo.MyNewTableName'
    DECLARE @DatabaseName AS VARCHAR(500)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(128);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run DDL statement to create View
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR

How to Drop Table from All the Databases from SQL Server

Scenario : How to Drop Table from All the Databases from SQL Server 

You are working as SQL server developer or SQL server DBA. You got this requirement " You need to create the scripts to drop the table from all the databases if exists in SQL Server". If you have small number of databases, let's say 5 or 10, It it not a bid deal to go to each Database and drop the table. But think about  a situation when you have 100's of databases and you have to drop the table from each database if exists. 

I have used the cursor with dynamic sql to loop through the databases and drop the drop if exists. 
You can filter the databases for which you want to run the script by add more filters to where clause in sys.database part of the code.

The below code is going to check if table exists and if it does, it is going to drop. I will suggest to run the script on Development, SIT, QA environments first and then on Prod. 


--Provide the DDL Statment that you would like to run to drop the table if exists in Database
  --Change the schema and table name in script as per your requirement
    DECLARE @DDL VARCHAR(MAX)
    SET @DDL='IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = ''''dbo''''
                 AND  TABLE_NAME = ''''MyNewTableName''''))
BEGIN
   Drop table dbo.MyNewTableName
END'
    DECLARE @DatabaseName AS VARCHAR(128)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run DDL statement to drop table
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR

How to create table in all the databases in SQL Server - SQL Server Scripts

Scenario:

You are working as SQL Server DBA or developer and you got this requirement" You have to create a table in all the databases on SQL Server Instance" If you have few databases, it might be each to change the Database scope in session and run the query but think about situation if you have 100's of databases and you have to create the table in every database.

Below script can be used to create table in all the Databases on SQL server. You can also filter the databases on which you would like to run the script. Please the DDL statement for your table create in below script and you are good to go.


--Provide the DDL Statment that you would like to run for Create table in Each Databsae in SQL Server
    DECLARE @DDL VARCHAR(MAX)
    SET @DDL='Create table dbo.MyNewTableName (id int, name varchar(100))'
    DECLARE @DatabaseName AS VARCHAR(500)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run DDL statement to create table
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR



Once you run above script, dbo.MyNewTableName will be created in all the database which were returned by select * from sys.databases.