Best way to deploy user defined functions to multiple databases in SQL 2005

Question

Hi,

I manage a server with around 400+ databases which have the same database schema, i wish to deploy a custom clr/.net user defined function to them all, is there any easy way to do this, or must it be done individually to each database?

Best Regards, Wayne

Answer 1

I think if you create it in master (in MSSQL) it can be referenced from any other db in that instance. Certainly seems to work for Stored Procs anyway.

I should add that this only works if the databases are all on the same server instance...

You could write a small app to deploy the udf to the master of each SQL server instance if all 400 reside on multiple servers.

Answer 2

i'd just create a dynamic script to create it on each database. but after that i'd put it in the modal databases so that all new databases are created with it.

you could also use the same script to push out changes if the function ever gets modified.