Rebuilding a Stored Procedure From System Tables MS SQL

Rebuilding a Stored Procedure From System Tables MS SQL

By Strictly-Software

Quite often I find "corrupted" stored procedures or functions in MS SQL that cannot be opened in the visual editor.

The usual error is "Script failed for StoredProcedure [name of proc] (Microsoft.SqlServer.Smo)"

This can be due to comments in the header of the stored procedure that confuse the IDE or other issues that you may not be aware of.

However if you get this problem you need to rebuild the stored procedure or function ASAP if you want to be able to edit it visually again in the IDE.

The code to do this is pretty simple and uses the sys.syscomments table which holds all the text for user-defined objects. We join on to sys.sysobjects so that we can reference our object by it's name.

When you run this with the output as "Results To Grid" you may only get 1-4+ rows returned and the data isn't formatted usefully for you to just copy and paste and rebuild.

Therefore always ensure you chose "Results To Text" when you run this code.

Make sure to change the stored procedure name from "usp_sql_my_proc" to the name of the function of stored procedure you need to rebuild!

SELECT com.text
FROM sys.syscomments as com
JOIN sys.sysobjects as sys
 ON com.id = sys.id
WHERE sys.name='usp_sql_my_proc'
ORDER BY colid