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
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.
Below T-SQL query can create the view in MYDB from/by accessing the MASTER database.
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)
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.
'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
--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
@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
@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
SET @SQLVIEW =' EXEC ' +@DatabaseName+'.'+'SYS.'+'SP_EXECUTESQL @STMT= N''' + @SQLCMD +''''
--PRINT @SQLVIEW
EXEC (@SQLVIEW)
--CREATING VIEW
--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
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