469,347 Members | 6,633 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,347 developers. It's quick & easy.

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

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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Philippe Poulard | last post: by
1 post views Thread by smitty mittlebaum | last post: by
9 posts views Thread by Patrick | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.