function scope and how to call with ::fn_myfunction

Question

Post reply

function scope and how to call with ::fn_myfunction

Lowell

SSC Guru

Points: 323518
More actions
March 29, 2007 at 12:42 pm

#63058

Ok here's a bit more advanced question:
Say i create a procedure in master that references sysobjects and syscolumns.
I name the procedure sp_**
if i reference that proc from another database, the scope for sysobjects becomes currentdatabase.db.sysobjects...not master.dbo.sysobjects.
say I do the same with a function: create in master, references sys* tables of some sort...try and use the function, and it's scope is restricted to master.dbo.sys***
kind of makes sense, since when you partially qualify it as dbo.fn_find, I'll conceed.
you can call some functions via select * from ::fn_function name, like this for example:
select top 5
cast(o.[name] as char(30)) as 'table_name',
cast(c.[name] as char(30)) as 'column_name',
cast(t.[name] as char(30)) as 'column_type ',
cast(c.[length] as char(30)) as 'column_length ',
cast(e.value as char(30)) as 'column_description'
from sysobjects o inner join syscolumns c on o.id = c.id
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',
N'user',N'dbo',N'table',null, N'column', null) e on c.name COLLATE Latin1_General_CI_AS = e.objname
left join systypes t on c.xtype = t.xtype
order by c.colorder
the extended properties are stored in the current database, not in master; the function is built in to the server i think, as I cannot do sp_helptext FN_LISTEXTENDEDPROPERTY....how can I get a function to behave like that with the :: calls? I tried enabling system marking, but that did not work...
here's a proc and function as an example...note that it does set system marking on, but if you try to use the ::, it does not work.
oh yeah...the reason i want to do this: i have a function that returns the DDL of any table or object...but i want to put it in master, and not in each database...it's just kind of handy.
CREATE PROCEDURE sp_find
@findcolumn VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON
    SELECT sysobjects.name AS TableFound,syscolumns.name AS ColumnFound
      FROM sysobjects
        INNER JOIN syscolumns ON sysobjects.id=syscolumns.id
      WHERE sysobjects.xtype='U'
      AND   (syscolumns.name LIKE '%' + @findcolumn +'%'
      OR    sysobjects.name LIKE '%' + @findcolumn +'%' )
    ORDER BY   TableFound,ColumnFound
END
GO
--Turn system object marking on
EXEC master.dbo.sp_MS_upd_sysobj_category 1
GO
CREATE FUNCTION fn_find
(@findcolumn VARCHAR(50) )
RETURNS @tblArray TABLE
    (
    TableName varchar(60),
    ColumnName varchar(60)
    )
AS
BEGIN
    INSERT INTO @tblArray(TableName,ColumnName)
      SELECT sysobjects.name AS TableFound,syscolumns.name AS ColumnFound
        FROM sysobjects
         INNER JOIN syscolumns ON sysobjects.id=syscolumns.id
        WHERE sysobjects.xtype='U'
        AND   (syscolumns.name LIKE '%' + @findcolumn +'%'
        OR    sysobjects.name LIKE '%' + @findcolumn +'%' )
      ORDER BY   TableFound,ColumnFound

     RETURN
END
GO
--Turn system object marking off
EXEC master.dbo.sp_MS_upd_sysobj_category 2

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

...EXEC master.dbo.sp_MS_upd_sysobj_category 1 .. isn't a best practise !

Why marking a propriatary proc as system proc ?

I don't think it works the same way for functions as for sprocs

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Lowell SSC Guru Points: 323518 More actions · Answer 2

definitely not a best practice, i admit...I was just showing by example that just because something is a marked as a system function, does not make it available to ::fn_ calls......but having to put the same function in 30 databases on a server, instead of once in master, is just repetitive and annoying...i'd rather it was in one place.

And obviously, anything i can do in a function, and can adapt into a procedure to do nearly the same thing, it's just i wanted the ease of use to call select ::fn_myfunction(param)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

There is a way, unfortunately, I can't remember how or where I saw it. I will, during my free time, try and see if I can find it again and if I do, I'll let you know.

Lowell SSC Guru Points: 323518 More actions · Answer 4

I found it; it's two pieces of the puzzle...it must be a system function, and it ALSO must be owned by 'system_function_schema' ... NOT dbo:

http://msdn2.microsoft.com/en-us/library/aa224829(SQL.80).aspx

SELECT UID FROM master.dbo.sysusers WHERE "NAME" = 'system_function_schema'

To see a list of all objects that this UID owns, run this query:

SELECT * FROM master.dbo.sysobjects WHERE UID = (SELECT UID FROM master.dbo.sysusers WHERE "NAME" = 'system_function_schema')

I'll try this out and let you know how well this works.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

that does rings a bell overhere

"If you don't use it , you tend to forget it"

Also keep in mind this may not work in the same way when migrating to sql2005.

(I didn't investigate that, because we're not using functions in this way)