SQL Server how to create a view in a specific database on a remote server

Question

I am writing a dynamic SQL statement to create view on a remote server in a specific database. The SQL statement I generate gets executed on the master database. If I use use the 3 part naming convention in the CREATE VIEW statement does not help.

--QUERY
SELECT 
    'IF OBJECT_ID(N'''+ ss.name +'.view_' + so.name + ''') IS NULL
        EXECUTE (''CREATE VIEW ' + ss.name + '.view_' + so.name + ' AS SELECT * FROM REMOTEDATABASE.' + ss.name + '.' + so.name + ''') AT [REMOTESERVER]' 
   FROM sys.objects so
   JOIN sys.schemas ss
     ON so.[schema_id] = ss.[schema_id] 
  WHERE so.[type] = 'U';

--Query Result
IF OBJECT_ID(N'dbo.view_Test') IS NULL 
    EXECUTE ('CREATE VIEW dbo.view_Test AS SELECT * FROM REMOTEDATABASE.dbo.Test') AT [REMOTESERVER]

Whatever the answer is it will be out of date as of SQL 2016 SP1 -- new command -- `CREATE OR ALTER VIEW `. See https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/ — Hogan, Jan 20 '17 at 19:42
Possible duplicate of [Sql Server equivalent to Oracle's CREATE OR REPLACE VIEW](http://stackoverflow.com/questions/163246/sql-server-equivalent-to-oracles-create-or-replace-view) — Hogan, Jan 20 '17 at 19:44
Using * in a view is not a good idea. If the table structure changes your view will NOT update itself to include the new columns. Even more confusing if a new column is added and another dropped the values in your view can shift columns. — Sean Lange, Jan 20 '17 at 19:58
@Sean Lange: I am not using SELECT *, I was using that just for deomostration. Xedni: If I include USE CREATE VIEW I get create view should be the only statement in the batch error. — ITHelpGuy, Jan 20 '17 at 20:36

score 1 · Accepted Answer · answered Jan 21 '17 at 18:55

1

To create a view in a specific database at a remote server, try something like this:

EXECUTE ('USE YourDatabase EXEC(''CREATE VIEW YourView AS SELECT 1 AS SomeColumn'')') AT YourRemoteServer

answered Jan 21 '17 at 18:55

Razvan Socol

5,426
2
20
32

This is a useful, hard answer to a useful, rare question. This deserves 1000 likes, not the difference between inner and left join. – George Menoutis Apr 05 '22 at 07:56

score -1 · Answer 2 · edited Aug 02 '17 at 22:31

-1

The below worked for me:

USE YourDatabaseName  

GO  

CREATE VIEW [ViewName]  
AS  
SELECT 1

edited Aug 02 '17 at 22:31

boop_the_snoot

3,209
4
33
44

answered Aug 02 '17 at 17:14

Rusty Howard

1

SQL Server how to create a view in a specific database on a remote server

2 Answers2