Posted by downwitch on 07/11/07 23:04

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-----

[Back to original message]