By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,812 Members | 856 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,812 IT Pros & Developers. It's quick & easy.

Can I reference the "active" database's system tables from a utility UDF?

P: n/a
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

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

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

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


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

-----END CODE-----

Jul 11 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.