Stored procedure text

SQL Server has a system view sys.syscomments that holds the sql text for all of your stored procedures, views, triggers, UDF's, default constraints and computed columns.

I was considering using BULK INSERT to import a file but hadn't used it for a couple years, so I wanted to search some old scripts to get the basic syntax. I Google'd "SQL Server stored procedure text" and found a short article by Pinal Dave SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object which mentions sp_helptext, but you need to know the name of the sp in order to find it; catch-22.

So I opened up sp_helptext and found it was using sys.syscomments to get the sql text; from there it was easy to write:

SELECT OBJECT_NAME(id), [text]
FROM sys.syscomments
WHERE [text] LIKE '%BULK INSERT%'

...which in my case returned 7 rows. Eureka!

It is important to note that [text] is nvarchar(4000) so in the event that your sql text is larger than that, there will be multiple rows for that item, and the order is found in the column colid. So then, to get the complete text for an sp named "my_sp" you would use this:

SELECT object_name(id), colid, [text]
FROM sys.syscomments
WHERE OBJECT_NAME(id) = 'my_sp'
ORDER BY 2

Now, sys.syscomments view does not have an identifier for the type of object, so if you normally use prefixes when naming your objects e.g. viw for views, trg for triggers etc. that will make it much easier to locate the desired sql text.