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

How to get DROP AND CREATE script for all INDEXES USING simple SELECT in SQL SERVER

Below T-SQL QUERY helps to get DROP and CREATE script for all INDEXES in SQL SERVER. Feel free to use it if needed,  Kindly let me know in case if you got a better and easy way of doing it (do not say 😊 that it can be done using SSMS, right-click on the database-->Tasks-->Generate Scripts...).

SELECT INDEX_NAME,'IF EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = '''+INDEX_NAME+''') DROP INDEX '+QUOTENAME(INDEX_NAME)+' ON ' + QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+
' CREATE '+ IS_UNIQUE  +INDEX_TYPE_DESC + ' INDEX ' +QUOTENAME(INDEX_NAME)+' ON ' + QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+ '('+[INDEX_COLUMNS]+' ) '+ 
  case when [INCLUDED_COLUMNS]<>'' then CHAR(13) +'INCLUDE (' + [INCLUDED_COLUMNS]+ ')' else '' end + CHAR(13)+'WITH (' + INDEXOPTIONS+ ') ON ' + QUOTENAME(FILEGROUPNAME) + ';' SQLCMD
 FROM ( 
SELECT DISTINCT SCHEMA_NAME(ST.SCHEMA_ID) TABLE_SCHEMA, ST.NAME TABLE_NAME, SIX.NAME INDEX_NAME,
CASE WHEN SIX.IS_UNIQUE = 1 THEN ' UNIQUE ' ELSE '' END AS IS_UNIQUE 
, SIX.TYPE_DESC COLLATE DATABASE_DEFAULT  INDEX_TYPE_DESC, 
  CASE  WHEN SIX.IS_PADDED=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END 
 + CASE WHEN SIX.ALLOW_PAGE_LOCKS=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END
 + CASE WHEN SIX.ALLOW_ROW_LOCKS=1 THEN  'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END
 + CASE WHEN INDEXPROPERTY(ST.OBJECT_ID, SIX.NAME, 'ISSTATISTICS') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END
 + CASE WHEN SIX.IGNORE_DUP_KEY=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF ' END
 --+ ', SORT_IN_TEMPDB = OFF '+ CASE WHEN IX.FILL_FACTOR > 0 THEN ',FILLFACTOR =' + CAST(IX.FILL_FACTOR AS VARCHAR(3)) ELSE ' ' END 
 AS INDEXOPTIONS,FILEGROUP_NAME(SIX.DATA_SPACE_ID) FILEGROUPNAME 
 ,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME)+ ' '+ CASE WHEN IXC.IS_DESCENDING_KEY =1 THEN 'DESC' ELSE 'ASC' END  AS 'data()'
 FROM SYS.TABLES T 
JOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_ID 
JOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_ID 
JOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID 
  WHERE IX.TYPE>0 AND (IX.IS_PRIMARY_KEY=0 AND IX.IS_UNIQUE_CONSTRAINT=0) AND IXC.IS_INCLUDED_COLUMN <> 1
AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAME
FOR XML PATH('')),'~~',', '), 1, 2, '') as [INDEX_COLUMNS] 
,ISNULL(STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME) AS 'data()' FROM SYS.TABLES T 
JOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_ID 
JOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_ID 
JOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID 
  WHERE IX.TYPE>0 AND (IX.IS_PRIMARY_KEY=0 AND IX.IS_UNIQUE_CONSTRAINT=0) AND IXC.IS_INCLUDED_COLUMN = 1
AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAME
FOR XML PATH('')),'~~',', '), 1, 2, ''),'') as [INCLUDED_COLUMNS] 
 FROM SYS.TABLES ST INNER JOIN SYS.INDEXES SIX ON ST.OBJECT_ID=SIX.OBJECT_ID 
 WHERE SIX.TYPE>0 AND ( SIX.IS_PRIMARY_KEY=0  AND SIX.IS_UNIQUE_CONSTRAINT=0) 
 AND ST.IS_MS_SHIPPED=0 AND ST.NAME<>'SYSDIAGRAMS'  ) A

Thank you,

Narasimha