SQL Server - Where Is "Sys.Functions"

SQL Server - where is sys.functions ?

I find UDFs are very handy and I use them all the time.

I'm not sure what Microsoft's rationale is for not including a sys.functions equivalent in SQL Server 2005 (or SQL Server 2008, as far as I can tell), but it's easy enough to roll your own:

CREATE VIEW my_sys_functions_equivalent
AS
SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF')  -- scalar, inline table-valued, table-valued

View all functions from the SQL Server database?

This will return all user-defined functions. I'm not sure what you mean by "build-in" functions.

SELECT * 
FROM sys.objects 
WHERE RIGHT(type_desc, 8) = 'FUNCTION'

OR

SELECT * FROM sys.all_objects where type in ('FN','AF','FS','FT','IF','TF')

Here are the types:

--AF = Aggregate function (CLR)
--C = CHECK constraint
--D = DEFAULT (constraint or stand-alone)
--F = FOREIGN KEY constraint
--PK = PRIMARY KEY constraint
--P = SQL stored procedure
--PC = Assembly (CLR) stored procedure
--FN = SQL scalar-function
--FS = Assembly (CLR) scalar function
--FT = Assembly (CLR) table-valued function
--R = Rule (old-style, stand-alone)
--RF = Replication filter procedure
--SN = Synonym
--SQ = Service queue
--TA = Assembly (CLR) trigger
--TR = SQL trigger 
--IF = SQL inlined table-valued function
--TF = SQL table-valued function
--U = Table (user-defined)
--UQ = UNIQUE constraint
--V = View
--X = Extended stored procedure
--IT = Internal table

Here is a list of all system stored procs:

http://msdn.microsoft.com/en-us/library/ms187961.aspx

Is there a system table or system view that lists all built-in functions in SQL Server?

I think it is YOUR rule of naming functions, as long as YOU are the developer. Which means your functions will have always your prefix in database, as mf_ which could mean myfunction_ and your user's function name. In your code always put that mf_ prefix in front of your user's function call, check if it exists and use it.

How to use system function in where SQL Server 2008?

Use a sub-query:

SELECT p1.* 
FROM   Tb_sales_entry_total_product  p1
WHERE  p1.Sno = (SELECT Max(p2.Sno) 
                 FROM  Tb_sales_entry_total_product p2) 

How to view a stored function - SQL Server

You can use sp_helptext command to view the definition. It simply does

Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

E.g;

EXEC sp_helptext 'StoredProcedureName'

EDIT:
If your databases or server are different then you can do it by specifying them as well

EXEC [ServerName].[DatabaseName].dbo.sp_helptext 'storedProcedureName'

Select system function names in SQL Server

We recommended that you use the system functions, Information Schema Views, or the system stored procedures to obtain system information without directly querying the system tables.

Source:

http://msdn.microsoft.com/en-us/library/ms191238%28v=sql.90%29.aspx

---

Related Topics