Parsing Visual Source Safe keywords embedded in Stored Procedures

When creating a stored procedure, if you place Visual Source Safe keywords inside the 'CREATE PROCEDURE' definition the keywords get stored in the SQL Server database along with the code in the syscomments system table. This is used to regenerate the original SQL code when you select 'script stored procedure' from Object Explorer.

The following code can be used to parse Visual Source Safe keywords embedded in Stored Procedures in the SQL Server Database.

SELECT

OBJECT_NAME(id),

substring([text],patindex('%$Revision:%',[text]),16),

substring([text],patindex('%$Archive:%',[text]),patindex('%$Revision:%',[text])-patindex('%$Archive:%',[text]))

FROM syscomments

WHERE [text] LIKE '%$Revision:%'

AND OBJECTPROPERTY(id, 'IsProcedure') = 1

order by OBJECT_NAME(id)