Support for MS_Description stored procedures
Bart Read
Posts: 997 Silver 1
Hi there,
Some of the most powerful features for SQL Prompt are the scaffolding support it provides for INSERT, UPDATE, and EXEC sproc statements. It makes it *so* much easier to write these statements, especially on tables with many columns, or sprocs with many parameters, when prompt will just scaffold out an entire statement for you, just leaving you to fill in the values. This and the wildcard expansion really are the killer timesavers for me.
Recently I've found myself writing a lot of stored procedures, far more than I'd like in fact because coding in SQL blows all the goats - it's just too repetitive and there's often very little advantage to functional decomposition to compensate. Anyhow, enough moaning about that.
My current problem is how to adequately document these sprocs. There are a couple of interesting articles on Simple Talk but, despite the YAML recommendations, I can't help thinking the best way is still to use the MS_Description extended property, and then you can rely on a tool like Red Gate's SQL Doc to actually generate some nice documentation for you out of that.
The thing is typing in all the EXEC sys.sp_addextendedproperty statements for a sproc and all its parameters is a monumental chore. I'd like some shortcut way to do this in SQL Prompt that would scaffold out all the statements in one go, rather than doing them one at a time. I'd also like it to correctly fill in the @levelXType and @levelXname values, etc., again to save a lot of typing, copying and pasting.
I did have a quick search through the forum to see if anyone else had mentioned it but it appears not so would you mind adding a feature request, please?
Thanks,
Some of the most powerful features for SQL Prompt are the scaffolding support it provides for INSERT, UPDATE, and EXEC sproc statements. It makes it *so* much easier to write these statements, especially on tables with many columns, or sprocs with many parameters, when prompt will just scaffold out an entire statement for you, just leaving you to fill in the values. This and the wildcard expansion really are the killer timesavers for me.
Recently I've found myself writing a lot of stored procedures, far more than I'd like in fact because coding in SQL blows all the goats - it's just too repetitive and there's often very little advantage to functional decomposition to compensate. Anyhow, enough moaning about that.
My current problem is how to adequately document these sprocs. There are a couple of interesting articles on Simple Talk but, despite the YAML recommendations, I can't help thinking the best way is still to use the MS_Description extended property, and then you can rely on a tool like Red Gate's SQL Doc to actually generate some nice documentation for you out of that.
The thing is typing in all the EXEC sys.sp_addextendedproperty statements for a sproc and all its parameters is a monumental chore. I'd like some shortcut way to do this in SQL Prompt that would scaffold out all the statements in one go, rather than doing them one at a time. I'd also like it to correctly fill in the @levelXType and @levelXname values, etc., again to save a lot of typing, copying and pasting.
I did have a quick search through the forum to see if anyone else had mentioned it but it appears not so would you mind adding a feature request, please?
Thanks,
Bart Read
Principal Consultant
bartread.com Ltd
Principal Consultant
bartread.com Ltd
Comments
The best place to log feature requests is on our UserVoice forum which will allow other users to vote on the idea, it'll also send you an email if we release a new build containing the feature and it helps when prioritising what we'll work on next.
One thing that might help in the meantime is creating a snippet for adding an extended property to a stored procedure similar to what Steve describes here.
We had a look at how we'd want this to work and we're not quite sure how it'd be triggered. INSERT and EXEC statements both have completion of the table/procedure, but sp_addextendedproperty doesn't have an obvious trigger. Maybe suggesting objects on the @levelXname and then filling in the other levels/types after one is selected?
Another possibility is being able to edit the MS_Description through the tooltip that appears when you hover over an object?
Thanks,
Aaron.