July 29, 2009 at 4:32 am
Hi guys
I'm trying to create a stored procedure on quite a number of databases, was wondering if there is a quick way to doing this.
If i use msfofreachDB, the procedure requires a lot of quotes.
July 30, 2009 at 1:32 am
You coud create it in one of your databases ad then create a cursor for master.dbo.sysdatabases. In the cursor fetch loop you could run sp_helptext (another cursor needed to get all the rows into a single varchar variable) and then run the text with EXEC.
Example:
USE firstdb;
GO
CREATE PROCEDURE myProc
AS
BEGIN
SELECT * FROM ...
END
GO
DECLARE @text_data TABLE (proc_text nvarchar(255))
DECLARE @name varchar(128)
DECLARE @procText varchar(8000)
DECLARE @sql varchar(8000)
DECLARE cur CURSOR STATIC FORWARD_ONLY
FOR
SELECT name FROM master.dbo.sysdatabases
WHERE NAME NOT IN ('master', 'model', 'msdb', 'tempdb', 'firstdb')
INSERT @text_data
EXEC sp_helpText 'myProc'
SET @procText = ''
SELECT @procText = @procText + proc_text
FROM @text_data
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE ' + @name + char(13) + 'GO' + char(13)
SET @sql = @sql + @procText
EXEC(@sql)
FETCH NEXT FROM cur INTO @name
END
CLOSE cur
DEALLOCATE cur
The only limitation is the size of the procedure text: it can't be more than 8000 chars.
To go around it you should go with SQLDMO.
Hope this helps
Gianluca
-- Gianluca Sartori
July 31, 2009 at 6:12 am
Why not create it on the Master database. You could then call it from anywhere
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply