Add a User-Defined Function to Master DB?

Question

Add a User-Defined Function to Master DB?

Rich Mechaber

SSChampion

Points: 11130
More actions
September 29, 2008 at 8:50 am

#382883

I want to add a UDF to the master db that's callable from any database context, much the way creating a user-defined stored procedure with the "sp_" prefix to the master db will. I have some custom date manipulation functions, and it makes no sense to add them to any one database or, worse, to every database.
I know I could call this function as master.dbo.MyUDF(@thsidate), but what I'd like to do is just type MyUDF(@thisdate). Much cleaner to edit and maintain.
Thanks very much,
Rich Mechaber

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Chris_P SSCommitted Points: 1753 More actions · Answer 1

Personally, while it is very tempting, I never add UDFs to a system database. I create a "DBA" database where I stick my utilities, custom procs, etc. It is still callable from any database but you will have to qualify it when doing so.

Chris.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

I agree... call it "DBA" or "UTIL" or whatever... but avoid Master like the plague if you can. Just use the 3 part naming convention when you call it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rich Mechaber SSChampion Points: 11130 More actions · Answer 3

Thanks to both Chris and Jeff - I think I will take your recommendation and create a utility database for this purpose.

But, to my original question: is it possible to add functions to master that are callable without specifying the schema (dbo)?

And why avoid adding to master "like the plague?" Is the concern that it will interfere with other functions in master? That MS will add a function of that name in the future?

Thanks,

Rich

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Just about anything that begins with "sp_" willl be checked for in Master first. I've known people to make Calendar and Tally tables that begin with sp_ just to get that functionality.

The reason why I suggest avoiding anything in the Master database is two fold... the installation of a hot-fix, cumulative update, or service pack could either fail because of or overwrite any of your existing objects in Master. The other reason is, all you have to do is have one bad index or one mistake, and, BOOM! Master goes suspect and you spend a week trying to get your server back.

It's kinda like a Mongoose and a Cobra... the Mongoose normally wins, but every once in a while... you wanna be the Mongoose in that case? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chris_P SSCommitted Points: 1753 More actions · Answer 5

What Jeff said but also...

I develop several handy utilities/scripts that I eventually propagate to all of my servers. Having them in a small utility container makes it very easy to deploy to other servers and manage.

Chris.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 6

Thanks for the add'l info., Jeff - it makes sense, and I'm trying to learn why certain "standard practices" are, in fact, standards.

Your analogy to the mongoose is doubly relevant in this circumstance. Years ago, I camped on the island of St. John in the US Virgin Islands. The island is home to a large population of feral mongoose ("mongooses"? "mongeese"?). They were imported deliberately by European colonialists, desperate to rid themselves of the swarms of rats they'd inadvertently brought over in their ships' bilges. You guessed it: the rats were eliminated with dispatch, but the cure turned out to be worse than the disease, as it was impossible to kill off the mongooses. They ravage through trash barrels like weasel-sized wolverines, fearing nothing and nobody.

Lessons to me: we're never as smart as we think we are, and beware unplanned ramifications of a "good idea." Corollary: kick the trash barrel before sticking your hand in!

Yours,

Rich

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

rmechaber (9/30/2008)
Corollary: kick the trash barrel before sticking your hand in!

We used to have a saying in the Navy... buy lunch... leave the trash barrels alone. 😛

Anyway, thanks for the feedback, Rich.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)