examples - sql server create function return table

SQL Server-¿dónde está "sys.functions"?

Question

SQL Server 2005 tiene excelentes vistas de sys.XXX en el catálogo del sistema que utilizo con frecuencia.

Lo que me sorprende es esto: ¿por qué hay una vista de "sys.procedures" para ver información sobre los procedimientos almacenados, pero no hay una vista de "sys.functions" para ver lo mismo para las funciones almacenadas?

¿Alguien usa las funciones almacenadas? ¡Los encuentro muy útiles para, por ejemplo, columnas calculadas y demás!

¿Hay alguna razón específica por la que falten las funciones del sistema, o es solo algo que no se consideró lo suficientemente importante como para ponerlo en las vistas del catálogo del sistema? ¿Está disponible en SQL Server 2008?

Saludos, Marc

Answer 1

Encuentro que las UDF son muy útiles y las uso todo el tiempo.

No estoy seguro de cuál es la lógica de Microsoft para no incluir un equivalente de sys.functions en SQL Server 2005 (o SQL Server 2008, por lo que puedo ver), pero es bastante fácil de implementar:

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

Answer 2

Es un poco más detallado, pero esto debería hacer exactamente lo mismo:

select * from sys.objects where (type=''TF'' or type=''FN'')

Por lo que puedo ver, tampoco está en SQL Server 2008.

Answer 3

Esto es válido en 2008 R2 por lo que SSMS genera cuando scripts una DETECCIÓN de una función:

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
*/

Answer 4

Esto no agrega nada nuevo, pero encontré lo siguiente más fácil de recordar:

select * from sys.objects where type_desc like ''%fun%''

Answer 5

Otra forma de enumerar funciones es hacer uso de las vistas INFORMATION_SCHEMA.

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

Según el sitio web de Microsoft "Las vistas del esquema de información proporcionan una vista interna, independiente de la tabla del sistema de los metadatos de SQL Server. Las vistas de esquema de información permiten que las aplicaciones funcionen correctamente aunque se han realizado cambios significativos en las tablas del sistema subyacentes". En otras palabras, las tablas del sistema subyacentes pueden cambiar a medida que SQL se actualiza, pero las vistas deben seguir siendo las mismas.

Answer 6

Para ampliar la respuesta de @ LukeH, para devolver las definiciones de funciones también se requiere una unión a la tabla sys.sql_modules . Entonces la consulta para esto es:

SELECT O.name as ''Function name'', M.definition as ''Definition'', O.object_id
FROM sys.objects as O INNER JOIN sys.sql_modules as M
    ON O.object_id = M.object_id
WHERE type IN (''FN'', ''IF'', ''TF'')  -- scalar, inline table-valued, table-valued

donde lo anterior muestra el nombre de la función, su definición y el identificador del objeto, respectivamente.

Answer 7

Para obtener una descripción más completa de las funciones escalares, incluido el propietario y el tipo de devolución:

SELECT f.name, s.name AS owner, t.name as dataType, p.max_length, p.precision, p.scale, m.definition
FROM sys.objects f
JOIN sys.schemas s on s.schema_id = f.schema_id
JOIN sys.parameters p on p.object_id = f.object_id AND p.parameter_id = 0
JOIN sys.types t ON t.system_type_id = p.system_type_id 
JOIN sys.sql_modules as m ON m.object_id = f.object_id
WHERE type=''FN'';

Answer 8

por cierto, ¿no te gustaría incluir type = ''FS''?

name    type    type_desc
getNewsletterStats  FS  CLR_SCALAR_FUNCTION

eso es con lo que se corresponde el elemento en sys.objects para mi UDF que se deriva de una DLL externa

Answer 9

prueba esto :

SELECT * FROM sys.objects
where type_desc = ''SQL_SCALAR_FUNCTION''

Answer 10

SQL 2000 específico, ligero ajuste para el nombre del objeto:

SELECT *
FROM sysobjects
WHERE type IN (''FN'', ''IF'', ''TF'')

O

SELECT *
FROM dbo.sysobjects
WHERE type IN (''FN'', ''IF'', ''TF'')