How to delete custom functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to delete custom functions

Author

Topic

jihaes
Starting Member

5 Posts

Posted - 2007-11-14 : 13:39:49

If i created a bunch of scalar and table functions in my database (let's call it UnitTest).

if I run SELECT * FROM SYS.SYSOBJECTS WHERE TYPE='FN', it's only going to give me functions I created in the UnitTest database and not also from the masters or any other system defined functions right?

If I'm wrong, how do I get all the function names I created so that I can drop all of them without touching any system defined ones?

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-14 : 13:44:32

You might try:

SELECT 
	* 
FROM 
	INFORMATION_SCHEMA.ROUTINES 
WHERE 
	ROUTINE_CATALOG = 'UnitTest'
	ROUTINE_TYPE = 'FUNCTION'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 13:45:16

Do you think there is some place that stores every function you write, regardless of database?

You could try the undocumentet "sp_msforeachdb" stored procedure.
sp_msforeachdb 'SELECT * FROM ?.SYS.SYSOBJECTS WHERE TYPE= ''FN'''

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-14 : 13:48:41

This is one of the reasons why we use a specific naming convention for our objects. For UDFs, we use udf_. Then we can search using LIKE 'udf[_]%'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jihaes
Starting Member

5 Posts

Posted - 2007-11-14 : 14:03:02

Thanks for your suggestions.
tkizer, not every user goes by those naming conventions. So how would you know which functions are user created? Do either of the above statments work? Is that safe?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-14 : 14:04:02

My point is that we don't create any custom UDFs unless they follow the udf_ naming convention.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources