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
Answer by Jon Crawford ·
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.
--Item like '--%'