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

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.