I noticed that, in my DacPac, after a deployment, two stored procs were given a modified date on sys.objects, almost to the same millisecond. However, I would only expect one of them to have this, since I only updated one proc. The other proc that was updated calls the one that was updated, but I didn't modify it directly.

Has anyone seen this behavior? Is it expected with DacPac, that the calling stored proc gets "modified" even though there is no actual code change, or could it be something else?

My concern is for a number of reasons, mainly related to QA people questioning this, and ultimately it isn't a good idea to see a proc has been modified when you can't explain why.

Any help appreciated.


Yes, it's expected.

If StoredProcA calls StoredProcB, and StoredProcB is modified, then the DacPac deployment will:

  • deploy the changes to StoredProcB
  • update the metadata for StoredProcA

What that looks like in the generated deployment script is:

PRINT N'Altering [dbo].[StoredProcB]...';

ALTER PROCEDURE [dbo].[StoredProcB]
your new stored proc body here
PRINT N'Refreshing [dbo].[StoredProcA]...';

EXECUTE sp_refreshsqlmodule N'[dbo].[StoredProcA]';

It's the call to sp_refreshsqlmodule that updates the modified date on StoredProcA. The procedure hasn't been redeployed, but it's underlying metadata may have.

In your case, nothing has really changed. The whole sp_refreshsqlmodule thing would be necessary if you updated a user-defined type that was used a parameter to StoredProcA, for example. It's not necessary in this situation, but SSDT doesn't have complex rules about when to refresh dependent modules - it just updates them all if any have changed.

  • can I just say, you are amazing. Makes perfect sense, thank you! – datadawg2000 Apr 16 '20 at 2:55
  • Aw, thanks @datadawg2000 😁 Glad I could help! – Josh Darnell Apr 16 '20 at 3:20

Your Answer

