Finding the source code

When you write a stored procedure, view, function or trigger in SQL Server, you might be wondering how an application like Enterprise Manager shows the source code for the object that we created. In this article, we will see the location where SQL Server stores the source code and how to get at it.
 
You can get the source code of an object (like a stored procedure) using two methods:
  • By using information in the syscomments table.
  • By using the information returned by the sp_helptext system stored procedure.

Let's see how to use each of these methods. But before that, create the following stored procedure that will help us in executing the relevant commands.


IF (OBJECT_ID ('dbo.GetAllAuthors') IS NOT NULL)
	DROP PROCEDURE dbo.GetAllAuthors
GO
CREATE PROCEDURE dbo.GetAllAuthors AS
BEGIN
	SELECT au_id, au_lname, au_fname, phone, address, city
		FROM authors
END

Now, let's see how to get the source code for the above procedure using the syscomments table. Execute the following query:


BEGIN
	SELECT text FROM syscomments WHERE OBJECT_NAME(id) = 'GetAllAuthors'
END

When you execute the above T-SQL, you can see the source code for the GetAllAuthors procedure. Note that the source code is stored in a column called text in the syscomments table. One thing to keep in mind is that the text column in syscomments is only 4000 characters long (actually 2000, since it is an NVARCHAR column). This means that if you have a procedure that exceeds this limit, you will get multiple lines of text after executing the above command. Thus, you need to aggregate the lines and then process the result.

Now, let's see how to get the same source code using sp_helptext. Execute the following query:


BEGIN
	SET NOCOUNT ON

	-- Create a temporary table and then execute the command
	CREATE TABLE #tempTable (sourceCode NTEXT)
	INSERT INTO #tempTable EXEC sp_helptext 'dbo.GetAllAuthors'

	-- Select the results from the temporary table and then
	-- delete the temporary table
	SELECT * FROM #tempTable
	DROP TABLE #tempTable
END

This method is slightly more involved, but the advantage we have here is, we do not need to deal with multiple result sets as given by the syscomments table. The sp_helptext command is the preferred method to get the source code, since it is a documented stored procedure and Microsoft might tomorrow enhance it to suppport other feature, whereas the syscomments method is more low-level and should generally be avoided.

Home