SQL Server Performance Forum – Threads Archive

DDL using Strored Procedure in another database

Hi everyone. A week ago, I was trying to create a view using SQL stored procedure located in another database. However, unlike a table, you CANNOT create a view (or index, etc.) directly by appending a database name as prefix (i.e. CREATE VIEW Database1.dbo.ServiceView). I guess I wasn’t thinking hard enough <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />. I know I was in the right direction of using SP_EXECUTESQL but was not quite there. Fortunately, Hari Prasad (MVP) was kind enough to help me. And to share it to you as Brad McGehee (MVP) suggested, here it is. I’ll used Hari’s example. Again, thanks to the Hari and Brad. use database2 go Create procedure dbo.proc1 @dbname varchar(20) as begin Declare @SQL varchar(1000) SET @sql = ‘USE ‘ + @dbname + ‘ EXEC sp_executesql N”CREATE VIEW myView AS SELECT * FROM dbo.Sysobjects”' EXEC (@sql) end GO –Execute the below command to create the view in master db:- EXEC proc1 ‘Master' go

Appreciate your feedback, in general its rare that the originator will appreciate the help. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for sharing such code Madhivanan Failing to plan is Planning to fail

Here’s another version of the very same thing
USE Tempdb
DECLARE @DBNAME SYSNAME
DECLARE @sql NVARCHAR(100)
SET @DBNAME = ‘Northwind’
SET @sql = QUOTENAME(@DBNAME) + N’..sp_executesql’
EXEC @sql N’CREATE VIEW dbo.ViewOrders AS SELECT * FROM Orders’
SELECT * FROM Northwind.dbo.ViewOrders
USE Northwind
DROP VIEW dbo.ViewOrders You can wrap this, of course, inside a stored procedure if you like. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

]]>