DDL using Strored Procedure in another database | SQL Server Performance Forums

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.<br /><br />use database2<br />go<br /><br />Create procedure dbo.proc1 @dbname varchar(20)<br />as<br />begin<br />Declare @SQL varchar(1000)<br /><br />SET @sql = ‘USE ‘ + @dbname + ‘ EXEC sp_executesql N”CREATE VIEW myView AS SELECT * FROM dbo.Sysobjects”'<br />EXEC (@sql)<br />end<br />GO<br /><br />–Execute the below command to create the view in master db:-<br />EXEC proc1 ‘Master'<br />go<br />
Appreciate your feedback, in general its rare that the originator will appreciate the help. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
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
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
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)