September 6, 2016 at 3:52 am
Hi,
How do I spot a Multi Statement Valued Table Function.
Also the same for an In-Line Valued Table Function.
How do I tell if Multi is being used in my database what are the characteristics that determine that it is MSVTF.
Thanks
September 6, 2016 at 4:08 am
Multi-statement table valued functions can be found in sys.objects with type = 'TF':
SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + name
FROM sys.objects
WHERE type = 'TF'
Inline table valued functions have type = 'IF'
SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + name
FROM sys.objects
WHERE type = 'IF'
It's really hard to tell whether the function is used or not. You could look in the dependencies to spot usage in other procedures or functions or in the plan cache to spot usage from ad-hoc statements.
Multi-statement functions are those that return a table variable (@someTableName).
Hope this helps
Gianluca
-- Gianluca Sartori
September 6, 2016 at 4:10 am
If the function starts with the definition of a table like this:
...RETURNS @somename TABLE
(...
Then it's a multi-statement table valued function. You can read more about the three kinds of functions in the documentation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 6, 2016 at 5:33 am
Here is one way of doing this, the output is self-explanatory, uses the OBJECTPROPERTY function and the sys.sql_expression_dependencies view.
😎
-- EE Quick Static Code Analysis
SELECT
SM.object_id AS O_ID
,OBJECT_NAME(SM.object_id) AS O_NAME
,LEN(SM.definition) - (LEN(REPLACE(SM.definition,NCHAR(10),N''))) AS O_NUMBER_OF_LINES
,LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SM.definition,NCHAR(32),N''),NCHAR(9),N''),NCHAR(45),N''),NCHAR(10),N''),NCHAR(13),N'')) AS O_NUMBER_OF_CHARS
,(SELECT COUNT(*) FROM sys.sql_expression_dependencies SD
WHERE SD.referencing_id = SM.object_id
AND SD.referenced_database_name IS NULL)AS O_INTERNAL_DEPENDENCIES
,(SELECT COUNT(*) FROM sys.sql_expression_dependencies SD
WHERE SD.referencing_id = SM.object_id
AND SD.referenced_database_name IS NOT NULL)AS O_EXTERNAL_DEPENDENCIES
,(SELECT COUNT(*) FROM sys.sql_expression_dependencies SD
WHERE SD.referenced_id = SM.object_id)AS O_DEPENDENTS
,OBJECTPROPERTY(SM.object_id,'IsInlineFunction')AS O_IsInlineFunction
,OBJECTPROPERTY(SM.object_id,'IsProcedure')AS O_IsProcedure
,OBJECTPROPERTY(SM.object_id,'IsScalarFunction')AS O_IsScalarFunction
,ISNULL(OBJECTPROPERTY(SM.object_id,'IsSchemaBound'),0) AS O_IsSchemaBound
,OBJECTPROPERTY(SM.object_id,'IsTableFunction')AS O_IsTableFunction
,OBJECTPROPERTY(SM.object_id,'IsTrigger')AS O_IsTrigger
,OBJECTPROPERTY(SM.object_id,'IsView')AS O_IsView
FROM sys.all_sql_modules SM
WHERE SM.object_id > 0
ORDER BY O_NAME ASC
;
September 6, 2016 at 5:36 am
Thanks All.
Much Appreciated!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply