Deploying UDFs/SPs across all DBs I'm an accidental DBA with no scripting knowledge/background whatsoever. We have a bunch of different DBs (each client has their own) that have the same structure, but different data. That said, we regularly create functions that everyone needs to use. This means that I have to deploy these to each and every one of our 120+ DBs. How do I do that without 'manually' deploying it to every DB?

I have asked around about this before, but nothing was really helpful. The answers I got were 'use scripting' (not viable because I don't even know how to start), 'use RedGate's tool' (don't have the money for it and the company won't buy it), and 'don't use scalar UDFs to begin with' (completely unhelpful). 

I've also been told 'just put it in master, or some other DB and call out to that'. That's a good thought, but soon our permissions/logins will change and I don't think that one DB user will be able to call out to another DB (HIPPA and all that). 

Can anyone provide some guidance for a 'DBA' of my level?

Thanks

UPDATE: I have found that one can use sp_MSforeachdb for DDL Statements. Clearly, this is not best practice as sp_MSforeachdb is undocumented and not 'supported' by MS. As in, use at your own risk. It worked like a charm for me.
See example 2: https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

For those that do not want to go that route and are in a similar boat than me, use Johnathan Fite's PS Script provided in the answers section. That's a handy script to have, especially if you have more than one server/instance.

Thanks everyone!
You can try out this as given by user Cade Roux in an answer to the Stack Overflow question Common function / stored procedures for all databases:


  Depending upon what the SP actually does, you want to create the procedure in master, name it with sp_ and mark it as a system procedure:


From Running a master db residing stored procedure in current database context by Mladen Prajdić:

USE master
go
CREATE PROC sp_TestMasterAccess
AS
SELECT    * 
FROM    information_schema.tables
GO
-- SS2k5 provides a stored procedure to mark the object as system
EXEC sys.sp_MS_marksystemobject sp_TestMasterAccess
GO
EXEC sp_TestMasterAccess

go
USE AdventureWorks
EXEC sp_TestMasterAccess

go
USE master 
DROP PROC sp_TestMasterAccess