Just Learned - How to create the VIEW in another database

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

How to create the VIEW in another database

Oct 9 2012 12:00AM by Dimitri Sorokin

It is not possible to create the VIEW in another database directly - unlike CREATE TABLE, CREATE VIEW statement doesn't have a "database_name" - part in its syntax. But you can do it by calling sp_executesql stored procedure:

DECLARE @s nvarchar(1000)
SET @s = 'CREATE VIEW dbo.test AS SELECT * FROM sys.objects'
EXEC tempdb.dbo.sp_executesql @s
SELECT * FROM tempdb.dbo.test
SET @s = 'DROP VIEW dbo.test'
EXEC tempdb.dbo.sp_executesql @s

You can do it fully dynamically too:

DECLARE @s nvarchar(1000), @p nvarchar(1000)
SET @s = 'CREATE VIEW dbo.test AS SELECT * FROM sys.objects'
SET @p = 'tempdb.dbo.sp_executesql'
EXEC @p @s
SELECT * FROM tempdb.dbo.test
SET @s = 'DROP VIEW dbo.test'
EXEC @p @s

2 Comments

@dmitri, I could not understand what you want to tell.

The example you gave can be executed without making it dynamic.

CREATE VIEW dbo.test AS SELECT * FROM sys.objects

This statement can be executed directly to create a view in any database.

Create view adventureworks.dbo.test as select * from sys.objects

I am not able to create this view neither using simple create view statement nor using dynamic query.

commented on Oct 9 2012 4:23AM

kr.roopesh

817 · 0% · 35

I mean, that using simple CREATE VIEW command it is not possible to create the VIEW in another database - just in the current one. You can create table in another database using CREATE TABLE database.owner.tablename, but if you try to create VIEW by this way, you will get the error message 166: 'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.

And surely, the statement "Create view adventureworks.dbo.test as select * from sys.objects" wouldn't work - at least in MS SQL Server :) But You can do it even so:

DECLARE @s nvarchar(1000)
SET @s = 'Create view dbo.test as select * from sys.objects'
EXEC adventureworks.dbo.sp_executesql @s

commented on Oct 9 2012 6:09AM

Dimitri Sorokin

616 · 0% · 57

Your Comment

Sign Up or Login to post a comment.

"How to create the VIEW in another database" rated 5 out of 5 by 6 readers