List of all SPs and functions on a given DB 1

[yorge]

Hi Guys,

Can anybody suggest a query that will lists all SPs and functions which is NOT using temp tables with # notation?

Thanks,
Yorge

 

[yorge]

just to add, this will be use for a contained database purpose. What is needed actually is a list of uncontained SPs or functions which are NOT using temp tables with # notation.

 

[djj55]

This is not the best but I just grabbed an example:

SELECT o.[type] AS ObjType, o.name AS ObjName, sm.definition 
FROM sys.sql_modules sm
INNER JOIN sys.objects o ON sm.object_id = o.object_id
WHERE O.name NOT LIKE 'FN_%'
    AND O.name NOT LIKE 'SP_%'
    AND O.name NOT LIKE 'DT_%'
    AND sm.definition NOT LIKE '%#%'

I hope this gives you an idea.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[yorge]

thanks djj55, would like to ask you if you know how to extend this to just filter out SPs and functions that are uncontained entity in a contained database. Im using mssql 2012. Same idea, filter out uncontained SPs or functions which are NOT using temp tables with # notation.

 

[djj55]

Sorry I am using 2008 and I am unfamiliar with contained databases.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[yorge]

tnx anyway, I hope somebody can give me some inputs...