Physically, SQL Server tables are stored in ta database as a set of 8 KB pages. Table pages are stored by default in a single partition that resides in the PRIMARY default filegroup.
1 Answer. Show activity on this post. In SQL Server Management Studio (SSMS) look under the Programmability\Functions branch.
SQL Server system functions perform operations on and return information about values, objects, and settings in SQL Server. Some system functions are deterministic while other system functions are nondeterministic.
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
Another way to list functions is to make use of INFORMATION_SCHEMA views.
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'
According to the Microsoft web site "Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables". In other words, the underlying System tables may change as SQL gets upgraded, but the views should still remain the same.
This is valid in 2008 R2 per what SSMS generates when you script a DROP of a function:
SELECT *
FROM sys.objects
WHERE type IN (N'FN', N'IF', N'TF', N'FS', N'FT') ;
/*
From http://msdn.microsoft.com/en-us/library/ms177596.aspx:
FN SQL_SCALAR_FUNCTION
FS Assembly (CLR) scalar-function
FT Assembly (CLR) table-valued function
IF SQL_INLINE_TABLE_VALUED_FUNCTION
TF SQL_TABLE_VALUED_FUNCTION
*/
It's very slightly more verbose, but this should do exactly the same thing:
select * from sys.objects where (type='TF' or type='FN')
As far as I can see, it's not in SQL Server 2008 either.
This does not add anything new, but I found the following easier to remember:
select * from sys.objects where type_desc like '%fun%'
try this :
SELECT * FROM sys.objects
where type_desc = 'SQL_SCALAR_FUNCTION'
incidentally, wouldn't you want to include type = 'FS'?
name type type_desc
getNewsletterStats FS CLR_SCALAR_FUNCTION
that's what the item in sys.objects corresponds with for my UDF which is derived from an external DLL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With