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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Subscribe to get the latest posts sent to your email.
SQL Server slow? Let's fix it together in about four hours.
In my Comprehensive Database Performance Health Check, you share your screen, I guide you, and we fix your biggest performance troublemakers. No passwords. No remote access.
You keep every script, and the know-how to fix it yourself next time.
Want your whole team to learn it?
My Practical Workshop has no PowerPoint, just live demos. My AI Enablement Workshop shows your team where AI saves hours, and where it quietly costs them.
Over 400 customers have worked with me this way, and not one shared a password. See all consulting options.
23 Comments. Leave new
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.
declare @s varchar(100)
set @s=”
select @s=@s+description+’,’ from gender
if len(@s)>0
select left(@s,len(@s)-1)
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
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?
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.
THANKS
ERCAN B.
Hi,
The above script gives function_name, schema name and type
But, i need Parameters and Datatypes of the functions. Can you script it out please
Thanks.
You can use sys.all_parameters catalog view and put filter for function name
select * from sys.all_parameters where object_id = object_id (‘AddNumbers’)
we can user sp_helptext StoredprocedureName this command , shows the sp’s but
how to view the user defined function in sqlserver
HI All,
Please help me , I want to make a user define functions in sql server but i don’t know how to make this.
because there is a confusion in @parameter and define @variable why we use the this .
supposed we are using parameter in function then why we need to use variable .
if you will give me a relevant answer then it would be a big big help for me.
Regards
Anand Chaudhary
How to know Alteration failure details on a table valued function using Query
Hi All,
Please help me out , suppose some one is Alter a table valued function but due to some region its not Command Complete successfully, but latter on i want know details on that
1. What is error
2. Date and Time of Execution
Regard’s
Santosh Mohanty
unless you trace it, its not logged in any log.
Hi Pinal Dave,
How do we find out that a function is being used in the sql server.
Thank You
Madhusudhan A
Hi, I want to get the list of tables used by a UDF in SQL server. Can anyone please help me with the query?