question

HeatherStacco avatar image
HeatherStacco asked

Want to capture comments from a stored procedure in a result set.

I am trying to find a way to capture the comments from stored procedures in our SQL DB as a start to trying to gather info for our data governance and quality initiative. Helping to understand what was done and why and potentially using it for some documentation in our data/business/metadata catalog. I've found ways to replace them but am not savvy enough to transition that into capturing them.

Any help would be appreciated.

Thank you- HS

stored procedurescommentsmetadata
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Jon Crawford avatar image
Jon Crawford answered

You should force people to use a standard header or extended properties (or both) to make this easier to extract, but you could do something like this, look up the DelimitedSplit8K function from Jeff Moden, or if you're on SQL Server 2016, use String_Split to find them.

Problem is that there are two kinds of comments, so you'll have to do a couple different passes. this sample doesn't do that, just an example of how to find this. You might find a better place than INFORMATION_SCHEMA to pull this too.

SELECT ROUTINE_SCHEMA,ROUTINE_NAME

,Item,ItemNumber

FROMINFORMATION_SCHEMA.ROUTINES

CROSSAPPLY dbo.FN_DelimitedSplit8K(Routine_Definition,'--')

WHERE

--Item like '--%'

--AND

ROUTINE_NAME='SP_myStoredProc'

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.