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.
**** 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
Code
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
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)
Returns Varchar(10)
AS
BEGIN
Return NULL
END'
EXEC sp_msforeachdb @command
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).
N'USE ?;
exec(''CREATE Function dbo.MyFunction(@param int))
Returns VARCHAR(2000)
AS
BEGIN
RETURN NULL
END'')'
Hope that helps explain the problem I was having and why I had to use a "double dynamic" string.
