I have a set of custom CLR functions installed in single database. When I need to use these functions in queries/views/procedures on another database, I simply reference them by three-part name [db].[schema].[function]() and it works fine.
But I can't use them in computed columns because of error 4120 A user-defined function name cannot be prefixed with a database name in this context.
Ofcourse, there's no problem with adding computed column on the same database using two-part name [schema].[function]().
Any ideas how to solve this problem? Maybe I can create some kind of "shortcut" functions in every other database that would reference "original" ones? Or could I install these functions in one of system databases in a way that will enable me to call function using only it's name, just like left() or substring()?
For now, the script that I use to update and install new functions, does the same thing on every database that I need the functions in, and in model db, too. But I wish there was more elegant way to do this.
There is no more elegant way to do this. A computed column cannot rely on an external function (even if you hide it behind a synonym, which seems to be the kind of "shortcut" function you're after) - the problem is that schema stability can't be broken by cross-database relationships - what happens when the database is offline, or single_user, or gets dropped? This is similar to the reason a foreign key can't reference a table in another database and a table can't belong to a schema in another database.
You already know that you can't use a 3-part name (in SQL Server 2014 I get a different error):
Msg 207, Level 16, State 1
Invalid column name 'other_database_name'.
If you try to mask your function with a synonym, you get:
Msg 2788, Level 16, State 2
Synonyms are invalid in a schemabound object or a constraint expression.
So, deploy your function to all of your databases, or pre-calculate the values and insert and store them manually instead of relying on the computed column.