2

The sys.dm_exec_describe_first_result_set_for_object function takes an object (e.g. stored procedure) and determines the properties of the first result set.

Is there a similar function that can show me if a procedure is "readonly", which could indicate that it can be used properly with ApplicationIntent=readonly in the connection string?

JoeGaggler
  • 1,207
  • 1
  • 13
  • 28
  • 1
    AFAIK, no. Consider that procs might contain dynamic SQL or be nested, making it a challenge to determine if the proc actually changes data. – Dan Guzman Jul 06 '17 at 02:23
  • @DanGuzman, I was thinking that if SQL Server knows when it's safe to run a proc on a secondary readonly server that it might expose that information to clients in some way. If `applicationintent` is only a hint, then I'm wondering why I wouldn't want to put it on every connection string in hopes that the server would place readonly calls on the secondaries. – JoeGaggler Jul 06 '17 at 15:30

1 Answers1

0

Interesting question. SQL Server does not mark objects as read-only, that is set at a user rights level. The ApplicationIntent=readonly seems to be dealing with a situation where you are running Availability Groups and have a read-only replica for reporting usually. It would then route to that server.

Read Only Application Intent

Jesse
  • 865
  • 10
  • 18