2

When I generate a stored procedure script, SSMS additionally generates a comment containing the name of the object and a Script Date timestamp:

enter image description here

The timestamp is just the current time at the moment the script was generated, and I am trying to understand the point of it.

I could see SSMS including an automatic feature to capture when a stored procedure is created, or last updated, or ran, for that matter. Any of those might be of use to a programmer in their day-to-day activity. What is the point of including the time it is viewed, though?

I have tried searching for an answer across the Internet but nothing useful has come up.

  • 3
    I've taken the liberty of editing your question to remove unnecessary emotions and generally make it look less like a rant and more like a genuine question. I'm not insisting this is the best way to ask it, so feel free to edit it further as you see fit. It would be great, though, if you refrained from adding irrelevant remarks, however just and colourfully expressed they might be. Thank you. – Andriy M Feb 8 '17 at 9:52
8

Why not? It allows you to see when the script was created if you save it & reload another time. The others you mention (created/last modified/last ran) aren't so useful as they're subject to frequent change. And can be grabbed from system views when/if you do need them.

You can turn it off in Options here if you really want to: enter image description here

1

I would say the stored procedure itself probably does not include the comment with the current script date. SQL Server Management Studio adds that when you generate the view of the procedure. In my experience, the stored procedure itself starts with CREATE or ALTER and anything above that is added by SSMS when you look at the code.

1

The timestamp shows when you generated the script, which could be helpful in putting together 'deployment' scripts etc, or when looking at a scripted out version of a SP compared to more recent changes that have happened in the database outside of that script. ("This is the definition that was current as of..." - like including today's newspaper in a photo; of course unlike a photo you can change it in the .sql file if you really wanted to!). Usually scripting out the SP would be used to apply the SP to another environment or something like that, in which case it's useful to be able to audit when that script was generated.

It isn't stored in the metadata for the SP, it's just generated at the time you run the "script as..." command.

[Are you routinely doing "Modify" on a SP to view its definition, hence the ambiguity between what's the definition of the SP itself and what was generated by SSMS? Could I suggest you develop the habit of "Script As" > "Create" to avoid inadvertent changes, assuming you are on a connection that allows you ALTER permissions? - Although the "Create" script also includes the timestamp, but something about the wording just made me wonder.]

By the way: it does capture the time it was created, and last modified (check Object Explorer Details from the Stored Procedures node for the easiest way to see that). There isn't an inbuilt way to see when it was last run other than capturing execution plans or something like SQL Audit or Extended Events.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.