Check settings for stored procedures

There may be times you need to view database settings at the stored procedure level.
As an example, I have been working with filtered indexes which require the QUOTED_IDENTIFIER setting to be enabled. Therefore stored procedures that insert or update a table that has a filtered index will need the setting enabled.

You can use the below script to see what setting has been applied for the stored procedures. Use the filter to target the table you are after.

SELECT  
    SCHEMA_NAME(s.schema_id)  + '.' + s.name AS name,
    s.create_date, 
    s.modify_date, 
    OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
FROM sys.objects s  
WHERE  
    s.type IN ('P','TR','V','IF','FN','TF')
    --AND OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') = 1
	AND s.name like '%MyTable%'
ORDER BY SCHEMA_NAME(s.schema_id)  + '.' + s.name DESC  

I hope that helps!

Subscribe

0 comments