I'm trying to develop an "installation pack" for my servers, we are regularly adding new servers to our inventory as we host client servers. So to speed up my configuring of a new server to our standard build, I've been configuring a load of procs to apply standard settings across a range of things (maintenance, security, standard jobs etc).
Something I'm trying to do is build a procedure that will create a set of procs on the master database. There's not many, there isn't a major usage of master, it's just for 1 or 2 system wide procs. Arguably I could put this in another database and we'd all be happy, but I'd prefer them to be in master if possible.
The process I've been using is to push into a variable the command to build an object then executing the variable, something along these lines:
CREATE PROCEDURE [build].[usp_CreateGetDateView]
DECLARE @EXSQL AS VARCHAR(MAX)
SET @EXSQL = '
CREATE VIEW dbo.vwGetDate
SELECT GETDATE() AS Result
So the above will create a proc that when executed will create a view that returns the result of GETDATE(). It means I can repeat this for multiple objects and just run the proc.
What I cannot seem to be able to do is configure a proc that once run, creates a proc in the master database. Firstly, I cannot put "CREATE PROCEDURE master.dbo.[procname]" since you cannot specify the database and I cannot put "USE master CREATE PROCEDURE dbo.[procname]" since the CREATE command needs to be the first statement in a batch (and putting GO in doesn't work either).
Does anyone have any suggestions as to how I can do this or is it fundamentally not possible?
I accept that if I simply had a script somewhere with all of this in, I could simply run it and if that's the best option then that's what I'll do, but I'm interested in whether there is a way to get around this restriction?