How to retrieve the text of UDF in SQL Server 2005

In the previous versions of SQL Server, retrieving the text of User
Defined Functions was easy:
SELECT text FROM syscomments sc INNER JOIN sysobjects so ON sc.id =
so.id WHERE so.name = 'fn_dblog' ORDER BY sc.colid
For some reason the above statement doesn't work in SQL Server 2005 (it
works for procedures and for views, but not for functions).
How can I retrieve the text of User Defined Functions in SQL Server
2005?
TIA
Dariusz DziewialtowskiHi
No, it works very well for user's UDF as well. This udf is not created by
an user ,moreover if i'm mo mistaken it isnt supported by MS
However you can achive it by issuing
sp_helptext 'fn_dblog'
<dariusz.dziewialtowski@.gmail.com> wrote in message
news:1144554215.276141.57970@.u72g2000cwu.googlegroups.com...
> In the previous versions of SQL Server, retrieving the text of User
> Defined Functions was easy:
> SELECT text FROM syscomments sc INNER JOIN sysobjects so ON sc.id =
> so.id WHERE so.name = 'fn_dblog' ORDER BY sc.colid
> For some reason the above statement doesn't work in SQL Server 2005 (it
> works for procedures and for views, but not for functions).
> How can I retrieve the text of User Defined Functions in SQL Server
> 2005?
> TIA
> Dariusz Dziewialtowski
>|||Hi Uri,
Thank you for your help.

>This udf is not created by an user
Yes, I gave it only as an example.

>However you can achive it by issuing sp_helptext 'fn_dblog'
I didn't think of that - I have to retrieve the text programmatically,
from VB6 code, but if I cannot make the old method work - "SELECT text
FROM syscomments" - than I'll try to execute sp_helptext
programmatically.
Still it puzzles me why "SELECT text FROM syscomments" is failing for
User Defined Functions in SQL Server 2005.
Thanks again for your help.
Dariusz Dziewialtowski.|||(dariusz.dziewialtowski@.gmail.com) writes:
> Still it puzzles me why "SELECT text FROM syscomments" is failing for
> User Defined Functions in SQL Server 2005.
In general it isn't:
CREATE FUNCTION myudf() RETURNS int AS
BEGIN
RETURN (99)
END
go
SELECT text FROM syscomments WHERE id = object_id('myudf')
go
DROP FUNCTION myudf
works for me.
However, in your original post you had fn_dblog, and that function
has moved and no longer lives in master, as have all other system
procedures and system UDFs. They now live in the hidden resource
database.
Also beware that SQL 2005 completely changes how metadata is stored.
The system tables from SQL 2000 are now merely compatibility views
on top of the new catalog views. The catalog views in their turn
refers to the new system tables that are accessible outside system code.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Erland,
Thank you for your help - I was not aware about the changes in metadata
in SQL Server 2005. Thanks a lot for explaining them to me.
Dariusz Dziewialtowski.