March 27, 2008 at 3:21 am
I want to add the procedure in all database, is it possible to add it using sp_MSforeachdb.
If possible, can anyone help me by providing code snippets.
March 27, 2008 at 4:11 pm
Hi ezhil,
I battled with this one for a while. Try this out...
sp_MSforeachdb 'USE ? EXEC (''CREATE PROCEDURE TestProc AS BEGIN SELECT 1 END'')'
The only way I could get it to work is by using exec to dynamically send the create command and avoid "'CREATE/ALTER PROCEDURE' must be the first statement in a query batch."
In your testing, here is the DROP statement:
sp_MSforeachdb 'USE ? DROP PROCEDURE TestProc'
Careful, this is an undocumented procedure so test well. Also, it adds the procedure to every database on the server, including the system databases. Because it is added to model, it will be created in any database created on the server in the future.
Good luck.
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
March 27, 2008 at 5:29 pm
I would include some logic to prevent the sp from being created in the system databases. I would also check for existence; otherwise, you can get an error.
sp_MSforeachdb
'USE ?
--IF THE DATABASE IS NOT A SYSTEM DATABASE
IF DB_ID(''?'') > 4
BEGIN
--CREATE THE PROCEDURE IF IT DOES NOT EXIST
IF NOT EXISTS (SELECT 1 FROM sys.procedures WHERE NAME = ''TestProc'')
BEGIN
EXEC (''CREATE PROCEDURE TestProc AS BEGIN SELECT 1 END'')
PRINT ''PROCEDURE CREATED IN '' + ''?''
END
END'
March 27, 2008 at 11:05 pm
Thanks
March 29, 2008 at 7:14 am
But when i use spMSforeahdb to alter this procedure i got problem , i don't know what the reason.
sp_MSforeachdb
'USE ?
IF DB_ID(''?'') > 4
BEGIN
EXEC (''
ALTER PROCEDURE [dbo].[spg_TotalSizePerUser]
@Type nvarchar(50) = 'DashBoard'
AS
BEGIN
DECLARE @StQry AS nVarchar(Max)
SET @StQry = 'SELECT IsNull(SUM(IsNull(NativeFileSize,0)/1024),0) AS SourceSize,
(SELECT
(SELECT IsNull(SUM(IsNull((ImageFileSize)/1024,0) + IsNull((TextFileSize)/1024,0)),0) FROM FileInfo
INNER JOIN BatchInfo ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 1 AND BatchInfo.IsMarkedforDeletion=0 AND BatchInfo.IsFileUploadCompleted=1)
+
(SELECT IsNull(SUM((IsNull(NativeFileSize,0)/1024) + IsNull(ImageFileSize/1024,0) + IsNull(TextFileSize/1024,0)),0) FROM FileInfo
INNER JOIN BatchInfo ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 0 AND BatchInfo.IsMarkedforDeletion=0 AND BatchInfo.IsFileUploadCompleted=1) )AS ExtractedSize
FROM FileInfo
INNER JOIN BatchInfo ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 1 AND BatchInfo.IsMarkedforDeletion=0 AND BatchInfo.IsFileUploadCompleted=1'
IF @Type = 'CaseDetails'
BEGIN
SET @StQry = STUFF(@StQry,7,1,' BatchInfo.BatchId,') + ' GROUP BY BatchInfo.BatchId'
EXEC sp_Executesql@StQry
END
ELSE
BEGIN
EXEC sp_Executesql@StQry
END
END
'')'
END'
March 29, 2008 at 8:23 am
Your quotes are messed up. You have to put 2 single quotes around items the need to be in quotes. E.g. select ''adam''.
try this. I cant test this but it does parse.
sp_MSforeachdb
'USE ?
IF DB_ID(''?'') > 4
BEGIN
EXEC (''
ALTER PROCEDURE [dbo].[spg_TotalSizePerUser]
@Type nvarchar(50) = ''DashBoard''
AS
BEGIN
DECLARE @StQry AS nVarchar(Max)
SET @StQry = ''SELECT IsNull(SUM(IsNull(NativeFileSize,0)/1024),0) AS SourceSize,
(SELECT
(SELECT IsNull(SUM(IsNull((ImageFileSize)/1024,0) + IsNull((TextFileSize)/1024,0)),0) FROM FileInfo
INNER JOIN BatchInfo ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 1 AND BatchInfo.IsMarkedforDeletion=0 AND BatchInfo.IsFileUploadCompleted=1)
+
(SELECT IsNull(SUM((IsNull(NativeFileSize,0)/1024) + IsNull(ImageFileSize/1024,0) + IsNull(TextFileSize/1024,0)),0) FROM FileInfo
INNER JOIN BatchInfo ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 0 AND BatchInfo.IsMarkedforDeletion=0 AND BatchInfo.IsFileUploadCompleted=1) )AS ExtractedSize
FROM FileInfo
INNER JOIN BatchInfo ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 1 AND BatchInfo.IsMarkedforDeletion=0 AND BatchInfo.IsFileUploadCompleted=1''
IF @Type = ''CaseDetails''
BEGIN
SET @StQry = STUFF(@StQry,7,1,'' BatchInfo.BatchId,'') + '' GROUP BY BatchInfo.BatchId''
EXEC sp_Executesql @StQry
END
ELSE
BEGIN
EXEC sp_Executesql @StQry
END
END
'')
END '
March 30, 2008 at 10:09 pm
Haines, Eventhough it is executed against all the database in my server , it show me this error,
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'DashBoard'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near '
IF @Type = '.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'DashBoard'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near '
Is any way to get rid of this error?
March 31, 2008 at 4:44 pm
Did you change the quotes? Post your code, as it stands now.
March 31, 2008 at 9:14 pm
Although it is a doable task, I wonder why we have to do so. If we create a stored procedure in myDB after myProc, why cannot we use the script like,
EXEC myDB..myProc
?
April 2, 2008 at 5:22 am
sp_MSforeachdb
'USE ?
IF DB_ID(''?'') > 4
BEGIN
EXEC (''
ALTER PROCEDURE [dbo].[spg_TotalSizePerUser]
@Type nvarchar(50) = ''DashBoard''
AS
BEGIN
DECLARE @StQry AS nVarchar(Max)
SET @StQry = ''SELECT IsNull(SUM(IsNull(NativeFileSize,0)/1024),0) AS SourceSize,
(SELECT
(SELECT IsNull(SUM(IsNull((ImageFileSize)/1024,0) + IsNull((TextFileSize)/1024,0)),0) FROM FileInfo
INNER JOIN BatchInfo ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 1 AND BatchInfo.IsMarkedforDeletion=0 AND BatchInfo.IsFileUploadCompleted=1)
+
(SELECT IsNull(SUM((IsNull(NativeFileSize,0)/1024) + IsNull(ImageFileSize/1024,0) + IsNull(TextFileSize/1024,0)),0) FROM FileInfo
INNER JOIN BatchInfo ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 0 AND BatchInfo.IsMarkedforDeletion=0 AND BatchInfo.IsFileUploadCompleted=1) )AS ExtractedSize
FROM FileInfo
INNER JOIN BatchInfo ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 1 AND BatchInfo.IsMarkedforDeletion=0 AND BatchInfo.IsFileUploadCompleted=1''
IF @Type = ''CaseDetails''
BEGIN
SET @StQry = STUFF(@StQry,7,1,'' BatchInfo.BatchId,'') + '' GROUP BY BatchInfo.BatchId''
EXEC sp_Executesql @StQry
END
ELSE
BEGIN
EXEC sp_Executesql @StQry
END
END
'')
END '
April 2, 2008 at 7:22 am
You're doing way too much work! We do this all the time when we upgrade numerous databases to a new version of our software.
1. Put the procedure(s) and anything else in a file. We call it "UpgradeSchema.sql" and it has everything in it.
2. Using a ".cmd" (command prompt) script use SQLCMD to run the file into the desired databases.
The nice part is that it is repeatable and reusable.
April 2, 2008 at 9:00 pm
This main piece of your code does not make sense to me because there is no need to do subqueries if you are returning all aggregated data. Your query should look something like this:
SELECT IsNull(SUM(IsNull(NativeFileSize,0)/1024),0) AS SourceSize,
IsNull(SUM(IsNull((ImageFileSize)/1024,0) + IsNull((TextFileSize)/1024,0)),0)
+
IsNull(SUM((IsNull(NativeFileSize,0)/1024) + IsNull(ImageFileSize/1024,0) + IsNull(TextFileSize/1024,0)),0)
AS ExtractedSize
FROM FileInfo
INNER JOIN BatchInfo ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 1 AND BatchInfo.IsMarkedforDeletion=0 AND BatchInfo.IsFileUploadCompleted=1
April 2, 2008 at 10:13 pm
All right I did a litte more testing and it seems we need to double up the quotes in the dynamic sql. You need to have 4 single quotes on each side of a string.
The code below should work.
sp_MSforeachdb
'USE ?
IF DB_ID(''?'') > 4
BEGIN
EXEC (''
ALTER PROCEDURE [dbo].[spg_TotalSizePerUser]
@Type nvarchar(50) = ''''DashBoard''''
AS
BEGIN
DECLARE @StQry NVARCHAR(MAX)
SET @StQry = ''''
SELECT IsNull(SUM(IsNull(NativeFileSize,0)/1024),0) AS SourceSize,
IsNull(SUM(IsNull((ImageFileSize)/1024,0) + IsNull((TextFileSize)/1024,0)),0)
+
(SELECT IsNull(SUM((IsNull(NativeFileSize,0)/1024) +
IsNull(ImageFileSize/1024,0) + IsNull(TextFileSize/1024,0)),0)
FROM FileInfo
INNER JOIN BatchInfo
ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 0 AND
BatchInfo.IsMarkedforDeletion=0 AND
BatchInfo.IsFileUploadCompleted=1) AS ExtractedSize
FROM FileInfo
INNER JOIN BatchInfo
ON BatchInfo.BatchID=FileInfo.BatchID
WHERE IsSource = 1 AND
BatchInfo.IsMarkedforDeletion=0 AND
BatchInfo.IsFileUploadCompleted=1''''
IF @Type = ''''CaseDetails''''
BEGIN
SET @StQry = STUFF(@StQry,7,1,'''' BatchInfo.BatchId'''') +
'''' GROUP BY BatchInfo.BatchId''''
EXEC sp_Executesql @StQry
END
ELSE
BEGIN
EXEC sp_Executesql @StQry
END
END
'')
END
'
April 2, 2008 at 10:31 pm
Thanks, it satisfy my needs exactly. why the previous sp which use 2 single quotes doesn't work. Isn't any reason behind this?
September 15, 2009 at 4:52 am
Hello,
I have build a helper stored procedure for creating a stored procedure on all databases using sp_MsForEachDB procedure.
All that one should do is creating a sp in master database.
By calling this sp within the sp_msforeachdb procedure by using the target sp name as parameter, the target sp will be created in each database created on the related sql instance.
I hope this may be a solution for many cases.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply