I have to create the same function in over 90 databases. I have procedure that will loop through a list of the databases and using dynamic sql create the function...the problem is the CREATE FUNCTION needs to be the first command in the dynamic sql and so it errors if I try to add USE db first and the USE db has to be in the same executed sql so I'm stuck. Dynamically executing the USE db and then the CREATE FUNCTION doesn't work because the USE db is outside of the scope of the CREATE FUNCTION so the db in effect does not actually change. Does this make sense? Any suggestions on how to accomplish this?
And this is not a one time deal otherwise I'd just manually create the function in each db. This is going to need to be run several times a month probably.
Thanks for any help
I think you just have to use dynamic SQL within dynamic SQL, something like this:
Post the code you have and maybe someone will try to fix it for you.
Please use system stored procedure
this will go through all databases
**** PLEASE try this AND Let me know if it WOrks ****
Example 2: Execute A DDL Query Against All User Databases On A SQL Instance
--This statement creates a stored procedure in each user database that will return a listing of all users in a database, sorted by their modification date
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?
EXEC(''CREATE PROCEDURE pNewProcedure1 AS SELECT name, createdate, updatedate FROM sys.sysusers ORDER BY updatedate DESC'') END'
EXEC sp_MSforeachdb @command
Ah, this works. Thanks Scott. I tell you it was fun (NOT) getting all the quotes matched up for the strings in the function....
please let me know !!!!
what hapenned to my sp_msforeachdb ??
it does automatically for all databases
i have used it several times
i could show you if you are interested
Looks like you found some other way ..
I'm using sp_msforeachdb. I just needed to double execute the dynamic sql as ScottPletcher suggested.
Please explain don't understand
I have used several times. Maybe i can help
I need to use dynamic sql that does a lot of things in multiple databases. One of things that needs to happen is to create a function, but that happens late in the script. The CREATE FUNCTION command has to be the first statement of the sql string being executed so if you have something like this
DECLARE @command nvarchar(MAX)
SET @command =
N'CREATE Function dbo.MyFunction(@param int)
You can't add 'USE ?' to the front of it because the create function command has to be the first statement in the exectuted script. You can't have 'create function [?].dbo.MyFunction' because create function does not allow prefixing the function with the db name. So in order to get the dynamic sql to execute on each db I have to make it double dynamic like this where the dynamic command string first uses the database and then executes an embedded dynamic sql string to create the function. (double dynamic string).
exec(''CREATE Function dbo.MyFunction(@param int))
Hope that helps explain the problem I was having and why I had to use a "double dynamic" string.
I learn new things every second
this is one ..