Posted by Erland Sommarskog on 07/12/07 08:43

downwitch (downwitch@gmail.com) writes:
> I'm not sure if this is possible, and it's tough to search for via
> google, so...
>
> I have a user-defined function that checks whether a given column has
> a default value set on it or not, as an example of what I'm trying to
> do. (It reads the system tables sysobjects, sysconstraints, and
> syscolumns, and returns a table of records, empty or not.)
>
> I need to use this function across a number of databases. But if I
> create it in a "utility" database, so that I can reference it via
> MyUtilities..MyFunction() syntax, it reads the system tables of
> MyUtilities.
>
> Is there a way to
> 1. store the function in a utility database
> 2. invoke the function from another database
> 3. and have it read the system tables of the database that does the
> invoking?

I don't think so. You can do this with stored procedures, but that is an
undocumented and unsupported feature.

I would recommend putting this function in all databases, and compose
a script that makes it easy to deploy it to all databases.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]