Following script is very simple script which returns all the User Defined Functions for particular database.
USE AdventureWorks;
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Community Initiatives
About Pinal Dave
Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. Here is the list of the Pinal Dave's books.
Follow @pinaldave
Send +Pinal Dave an email at pinal@sqlauthority.com-
- 63,464,169 (63 Million+)
SQL in Sixty Seconds
SQL Books
Funny Index Video
SQLAuthority Links
My Homepage
Windows Live Blog
--------------------
Top Downloads
PDF Downloads
Script Downloads
Script Bank
Favorite Scripts
All Scripts - 1
All Scripts - 2
All Scripts - 3
Top Articles
Best Articles
Favorite Articles - 1
Favorite Articles - 2
--------------------
> SQL Interview Q & A <
SQL Coding Standards
SQL FAQ Download
--------------------
Jobs @ SQLAuthority
About Nupur Dave
Top 3 Commenters
-
through SQL query, i’ve to concatenate multiple rows into single row with comma-separator. Help me how to write a coding for it.
Ex. the column is like wise
ID Name
1 Arthi
2 Preethi
3 Madhu
now it should be Arthi,Preethi,Madhu in a single row.
Other method
select specific_name,specific_schema from information_schema.routines
where routine_type=’function’
Arthi
Try this
declare @s varchar(8000)
select @s=coalesce(@s+’,',”)+name from your_table
select @s
Also, your question is no way related to this article
If you have any general question to ask post at http://www.sqlserverperformance.com/forum
[...] 22, 2008 by pinaldave Few days ago, I wrote about SQL SERVER – Find All The User Defined Functions (UDF) in a Database. Regular reader of this blog Madhivanan has suggested following alternate method to do [...]
I need to be able to take the results of your query and make a script to recreate all those functions on another server…is that possible?
@Hank,
Follow this link for step by step procedure to use Database Publishing Wizard 2005/2008
http://products.secureserver.net/products/hosting/PublishingWithDPW.htm
Use this tool to script out all object, or specific object by owner, by type ….
Choose objects you want to script, tool will generate script.
Or
In Sql Server 2005
Right Click Database Name -> All tasks -> Generate script, Follow the wizard.
~ IM.
I thinks this will also work
SELECT b.name fncSchema, a.Name AS function_name
FROM sys.objects a
INNER JOIN sys.schemas b
ON a.schema_id = b.schema_id
WHERE TYPE in (‘FN’, ‘IF’)
@Kalpesh – it’s actually
SELECT b.name fncSchema, a.Name AS function_name, TYPE
FROM sys.objects a
INNER JOIN sys.schemas b
ON a.schema_id = b.schema_id
WHERE TYPE in (‘FN’, ‘IF’, ‘TF’)
Is there any such function that check exactly Numeric values. isnumeric() is there but it arithmetic operators as numeric like * , – , / , * , .
I want it should check 0-9 and return True else False
Try this logic
select case when col not like ‘%[^0-9]%’ then is_numeric else non_numeric end as col from your_table
select case when col not like ‘%[^0-9]%’ then is_numeric else non_numeric end as col from your_table
multiple value comman operator
select *into(select *from split(tablecolumnname)as number table1)from table1
Is there a way to find out the author of the user defined function?
Hi All,
I have posted one query lately but didn’t get any reply. So I am posting another post here.
Can you post a query where we can find any object like either it’s a funcion or a view or any other object in a server having n number of databases.It should mention the datbase name,schema and also shows the the dependencies like if it is used in any stored procedure/view or in any sql jobs.
[...] Find All The User Defined Functions (UDF) in a Database This is another blog post which straight heads towards script instead of any other writing. [...]
THANKS
ERCAN B.