Indicate if an object is SELECT only or contains data insert/update/delete operations
Often when reviewing someone else's objects I have to open them and scan through to determine if they are truly read only or if they contain data modification code (delete,update, insert, merge, etc...). It would be most helpful if SQL prompt could show a flag for read only vs data mod objects (when you hover over and click to open the summary window displaying it there would be ideal). It would be best if it could check dependencies (if the object called others)
Josh
shared this idea
-
Stephen
commented
Thanks for the query, Daniel. Very helpful in detecting objects with now-non-existent object references, and inefficient scalar-function usages. Worth the 42 seconds it takes to run.
Cheers!
-
Daniel White
commented
This might help, even if it isn't a change in SQL Prompt's behavior:
ALTER VIEW [Helpers].[ObjectReferences]
AS
SELECT DISTINCT s.schema_id referencing_schema_id ,
s.name referencing_schema_name ,
o.object_id referencing_object_id ,
o.name referencing_object_name ,
o.type_desc referencing_object_type ,
re.referenced_server_name ,
re.referenced_database_name ,
re.referenced_schema_name ,
re.referenced_entity_name ,
re.referenced_id ,
re.referenced_class ,
re.referenced_class_desc ,
re.is_caller_dependent ,
re.is_ambiguous ,
re.is_selected ,re.is_updated -- this is the important line
FROM sys.schemas s
JOIN sys.objects o
ON o.schema_id = s.schema_id
LEFT JOIN sys.extended_properties ep
ON ep.class = 1
AND ep.major_id = o.object_id
AND ep.name = 'microsoft_database_tools_support'
CROSS APPLY sys.dm_sql_referenced_entities(s.name + '.' + o.name , 'object') re
WHERE ep.value IS NULL;
GO -
Josh
commented
*Point of clarification- table variables and non-global temp tables should not be considered data modification events as they may be used inside an object but don't impact data in actual tables, etc.. A global temp table is more interesting, perhaps a separate flag could be displayed for those scenarios. Lastly, if it could distinguish data mod from object mod (dropping a table or altering it, etc..)