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

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'







[quoted text, click to view]

Hi Uri,

Thank you for your help.


[quoted text, click to view]

Yes, I gave it only as an example.


[quoted text, click to view]

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.

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.

(dariusz.dziewialtowski@gmail.com) writes:
[quoted text, click to view]

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at