Is it possible to create a view in other database with EXEC?

user_8039968 0
Viewed 210

I'm currently writing a stored procedure which shall be executed on database A. It loops over other databases and shall create a view there if it can not find it. Here's a snippet:

BEGIN
    IF OBJECT_ID('['+ @source_db+'].['+@source_schema+'].['+@current_table+']', 'V') IS NULL
    BEGIN
    print('source view not available for ' + @source_db ) 
    print('creating view')
    EXEC('USE ['+@source_db+']; create view ['+@source_schema+'].['+@current_table+'] as select * from [XYZ].[' + @dc_guid + '].[' + @current_table+']' )
    print('view created')
    END 
END

But the EXEC statement obviously not works, since the View Statement must be the first one of a batch. But separating the use command to another EXEC statement doesn't work either (I found out that both EXEC statements are completely separate from another). As far as I know (and also tried out) it is not possible to use the "Go" command within EXEC.

What else can I do to achieve this?

stored-procedures sql-server
2 Answers

A solution can be encapsulating the creation of the view inside another dynamic SQL which will be executed after the connection changed database.

DECLARE @dynamicSQL VARCHAR(MAX) = 

    'USE AnotherDatabase;

    EXEC(''CREATE VIEW dbo.TestyView AS SELECT 1 AS Something;'')'

EXEC (@dynamicSQL)

If printed...

USE AnotherDatabase;

EXEC('CREATE VIEW dbo.TestyView AS SELECT 1 AS Something;')

And successfully creates the view.

user_46082040

Also you can make no use of USE and just call sp_executesql from db that you are interested in.

Here is an example of how to create a view vDateView in every database (system databases excluded) if it does not exist:

exec sp_MSforeachdb
'
if db_id(''?'') > 4 and
 not exists 
(
select *
from ?.sys.objects
where type = ''v''
and name = ''vDateView''
)
exec ?.dbo.sp_executesql N''create view dbo.vDateView as select getdate() as dt''
'

In this code ?.dbo.sp_executesql executes sp_executesql in the context of ? database

user_76104560
Related