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?

Hope this makes sense. UDF code below. Thanks in advance for your
help.

-----BEGIN CODE-----
CREATE FUNCTION dbo.uColumnHasDefault (
@TableName varchar(255),
@ColumnName varchar(255)
)
RETURNS TABLE
AS

RETURN (

--DECLARE @TableName varchar(255), @ColumnName varchar(255)
--SELECT @TableName = Null, @ColumnName = Null
SELECT sc.id, sc.constid, sc.colid, so.name AS TableName, scol.name
AS ColumnName, so2.name AS ConstraintName, sc.status
FROM sysconstraints sc
INNER JOIN sysobjects so ON so.id = sc.id
INNER JOIN sysobjects so2 ON so2.id = sc.constid
INNER JOIN syscolumns scol ON scol.id = sc.id And scol.colid =
sc.colid
WHERE sc.status & 5 = 5
And so.name = IsNull(@TableName,so.name)
And scol.name = IsNull(@ColumnName,scol.name)

)
GO
-----END CODE-----

downwitch (downwitch (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx