Monday 30 September 2019

How to create a VIEW/TRIGGER in USER DATABASE from/by accessing MASTER database in SQL SERVER

Let's create one user database named "MyDB".

SET NOCOUNT ON 
GO 
USE MASTER 
GO 
DROP DATABASE IF EXISTS MyDB 
GO 
CREATE DATABASE MyDB 
GO




















Let's create one table in MyDB by accessing Master DB

USE master
GO
CREATE TABLE MyDB.dbo.MyTable1 (ID INT,NAME SYSNAME)
GO

































Let's try creating the view for the MyTable1 in MyDB by accessing Master DB, You would be getting below highlighted the (red colour) error.

USE master
GO
CREATE VIEW  MyDB.dbo.VW_MyTable1 AS
SELECT * FROM MyDB.dbo.MyTable1  (NOLOCK)

Msg 166, Level 15, State 1, Line 3
'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.

















Below T-SQL query can create the view in MYDB from/by accessing the MASTER database.

USE master
GO
--DECLARING VARIABLES
DECLARE @DatabaseName SYSNAME,@TABLE_CATALOG SYSNAME,@TABLE_SCHEMA SYSNAME,@VIEW_NAME SYSNAME,@TABLE_NAME SYSNAME,
@COLUMNS_LIST NVARCHAR(MAX),@SQLCMD NVARCHAR(MAX),@SQLVIEW NVARCHAR(MAX)
--SETTING UP VALUES
SELECT @DatabaseName='MyDB',@TABLE_CATALOG='MyDB',@TABLE_SCHEMA ='dbo',@TABLE_NAME = 'MyTable1',
@VIEW_NAME = 'VW_MyTable1',@COLUMNS_LIST = '*'
--DROP THE VIEW IF EXISTS
SET  @SQLCMD = 'IF EXISTS (SELECT * FROM '+QUOTENAME(@DatabaseName)+'.INFORMATION_SCHEMA.VIEWS  WHERE TABLE_NAME ='''''+@VIEW_NAME+''''' AND TABLE_SCHEMA = '''''+@TABLE_SCHEMA+''''') DROP VIEW '+QUOTENAME(@TABLE_SCHEMA)+'.'+QUOTENAME(@VIEW_NAME)

SET @SQLVIEW =' EXEC ' +@DatabaseName+'.'+'SYS.'+'SP_EXECUTESQL  @STMT= N''' + @SQLCMD +''''
--PRINT @SQLVIEW
EXEC (@SQLVIEW)
--CREATING VIEW
SET  @SQLCMD ='CREATE VIEW  '+QUOTENAME(@TABLE_SCHEMA)+'.'+QUOTENAME(@VIEW_NAME) +' AS SELECT '+@COLUMNS_LIST +' FROM ' +QUOTENAME(@TABLE_CATALOG)+'.'+QUOTENAME(@TABLE_SCHEMA)+'.'+QUOTENAME(@TABLE_NAME) + ' (NOLOCK) '

SET @SQLVIEW =' EXEC ' +@DatabaseName+'.'+'SYS.'+'SP_EXECUTESQL  @STMT= N''' + @SQLCMD +''''
--PRINT @SQLVIEW
EXEC  (@SQLVIEW)

















The same approach can be followed for creating/altering TRIGGERS. Kindly let me know in case if you have an any better approach. Thank you

No comments:

Post a Comment