SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object

This is another popular question I receive. How to see text/content/code of Stored Procedure. System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.

sp_helptext @objname = 'name'

sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name
Displaying the definition of a trigger or stored procedure
sp_helptext 'dbo.nameofsp'

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

About these ads

63 thoughts on “SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object

  1. Hi pinal dave

    Sp_helpText is very good option for viewing
    the sp’s, Trigger’s , View’s and functions

    but try one system table which is more interesting to all experiment persons like me

    Syscomments , this Table holds all information of all objects text

  2. Hi,

    I was looking to display stored proc. code for SQL server. Then landed your blog through Google search.

    Excellent and precise information!


  3. Hello, we lost our formatting (i.e. indentation) when we reterive text using sp_helptext.
    Is there any way to get exact formatting?

  4. hi pinal,

    can u please provide me whole sql command list such as sp_helptext, or other short cuts.

    please list out or give me link.

    thanks in advance.

  5. @Ashutosh

    In Books Online Search for the word : System Stored Procedures, check all stored procedure and get familiar with definition, sql syntax .

    Below are very short scripts that will give you some basic information

    /* ================================
    Impersonate yourself as other user.
    Execute as login = ‘new_login_name’ –
    Execute as user = ‘new_login_name’

    select suser_sname()
    Execute as login = ‘new_login_name’
    select suser_sname()
    revert — brings back to your original login/user.

    /* ================================
    Get the list of all databases
    ================================ */

    select * from sys.databases — SQL Server 2005
    Select * from sysdatabases — SQL Server 2000

    –Tip:When you are in SQL Server Management Studio or Query Analyzer, Press Ctrl + U, it will activate drop down will all user defined databases.

    /* ================================
    Get Current Database Name
    ================================ */
    Select Db_Name()

    /* ================================
    Get list of database files for a specific database
    ================================ */

    Select * from sys.sysfiles — sql server 2005
    select * from sysfiles — sql server 2000

    /* ================================
    Get the list of all logins in a server
    ================================ */

    Select * from syslogins — sql server 2005
    select * from sysxlogins — sql server 2000

    /* ================================
    Get Current logged in login Name
    ================================ */

    Select suser_sname()

    /* ================================
    Get the list of all users in a database
    ================================ */
    Select * from Sysusers — sql server 2000
    Select * from sys.Sysusers — SQL Server 2005

    /* ================================
    Get Current logged in User Name
    Select user_name()

    /* ====================================
    Get a list of all objects in a database

    Select * from sysobjects — sql server 2000
    Select * from sys.all_objects — SQL Server 2005

    /* ================================
    Get list of all user defined tables.

    Select * from information_schema.tables
    Select * from sys.tables — SQL Server 2005

    /* ================================
    To get list of all views
    Select * from sys.views order by create_Date desc — SQL Server 2005

    /* ================================
    To get list of all procedures,

    Select * from sys.procedures order by create_Date desc — SQL Server 2005
    To get list of columns
    Select * from Information_Schema.columns

    Get the definition (Original text) of procedure, view, function.
    Exec Sp_Helptext Object_Name
    Exec Sp_Helptext [owner.objectname]

    Get Table ,View structure, information about indexes, constraints, data type, data length.
    Exec Sp_Help object_name
    Exec Sp_Help [owner.object_name]

    Get all dependency on a particular object.
    Sp_depends object_name

    Get list of orphaned (sql server) users.
    Sp_change_users_login ‘report’ :

    /* ================================================================
    Get Object Level Permision for each user or db_role
    sp_helprotect @user_name = ‘name of the user’

  6. @Ashutosh

    You can download all of these and stick it to your desk, so that you need not remember anything, just see and start using these shortcuts….

    SQL Server 2000 – Query Analyzer Short Cuts



    SQL Server 2005 – SQL Server Management Studio Short Cuts


    SQL Server Cheat Sheet (Very good one page information)



    ~ IM.

  7. How can i see my own text as i do it in c by using printf command.

    is ther is any command for print text in sql storeprocedure.

  8. @Amit

    create proc usp_example1
    print ‘This is how you print it’

    Exec usp_example1

    drop proc usp_example1

    ~ IM.

  9. hi ,

    i have a problem with calling a user defined function from ‘masters’ database which has been defined in ‘user’ database. i am using SQL 2000 server

  10. How can I search all triggers in all databases for the existence of a specific string?

    I can select routine_definition from INFORMATION_SCHEMA.ROUTINES to see the sp code. Is there a similar table for triggers?


  11. This is how I figured out how to search all triggers in all databases for the existence of a specific string. This also shows how to only search specific databases. Im my case, I only want databases that start with SL, GR or TR.

    using DB_NAME() AS ”?” in the select will put the database name in the first column of the resultset so you know which database the results came from.

    sp_msforeachdb ‘
    WHERE left(”?”,2) IN(”SL”,”GR”,”TR”)
    USE ?
    SELECT DB_NAME() AS ”?”,OBJECT_NAME(id),text
    FROM syscomments
    WHERE [text] LIKE ”%mysearchstring%” AND OBJECTPROPERTY(id, ”IsTrigger”) = 1

  12. I forgot to note, the double ticks ” are not double quotes “. They are two single ticks ‘ next to eachother since the command passed to sp_msforeachdb is already single quoted.

  13. Hi

    i have upgraded sql 2008 from 2005, after upgraded i got many problem in reports, the alignments are not good and also page setup not correct, please explain me why it is happening, and how to resolved this problem.



  14. Sir,
    In SQL 2000 ,how can we get the text of all Stored Procedure together ? In SQL 2005, I was able to get it from this code ,but there are some structures which are not present in SQL 2000 like (select * from sys.sql_modules m )

    The code for SQL 2005

    ALTER PROCEDURE [dbo].[usp_SearchStoredProcedure_Generate_ViewSPText]
    @SearchTerm VARCHAR(1000)
    SET @SQL = ”
    SELECT @SQL = @SQL + ‘ EXEC sp_helptext ”’ + s.name+’.’+OBJECT_NAME(m.object_id)+””
    FROM sys.sql_modules m
    INNER JOIN sys.objects o ON o.object_id = m.object_id
    INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE Definition LIKE ‘%’+@SearchTerm+’%’ AND
    OBJECTPROPERTY(m.object_id, ‘IsProcedure’) = 1;
    EXEC sp_executesql @SQL


    This is for calling them in single column

    – exec storedprocedureexecution

    ALTER procedure [dbo].[storedprocedureexecution]


    create table #temp
    SPName varchar(max)


    –insert into #temp exec dbo.usp_SearchStoredProcedure_GenerateHelpText ”
    insert into #temp exec dbo.usp_SearchStoredProcedure_Generate_ViewSPText ”

    select * from #temp


  15. hi, i want to display data in form.i have used labels and textboxes in forms and want to retreive data from my database to get data in the textboxes according to userid.i m able to reteive userid in page but now i m not gettin g how to get values in my textboxes from database. im using ASP.net 3.5 with C# and Sql server 2005.
    kindly help me.

  16. I have written a storeprocedure in a well formatted manner, and after saving I try to open the same procedure using the “sp_helptext “. Which actually opens the procedure but formatting is destroyed.
    So…What Is Solution For That

  17. I Have One Table [BIODATA] In Which One Field Like [Emp_Photo] With image Datatype And I Store Employee Image As Binary In This Table
    In This Table Have About 2000 Employee Record With Image

    When I Write [Select * From Biodata] It Will Take Large Amount Of Time For Complete Execution

    I Write [Select Emp_Code,Emp_Name..... From Biodata] Except [Emp_Photo] Field
    In Few Second It Will Execut

    So.. Any Solution For Query With [Image] field Can Be Faster

  18. ALTER VIEW [dbo].[vwProviderUtilization]
    dbo.UsageHourseMinutes(O.AllocationStartDate, ISNULL(O.ActualReleaseDate,GETDATE())) AS Utilized_Time

    this my view, if ActualReleasedate is null then am taking the getdate, if user select the date before the Getdate the utlizied time will be calculated on the getdate it rong how to solve the problem not in storeprocedure i need the result in veiw..

  19. How to view the stored procedure definition other than query in Query analyzer.Do you other other options .Example right click on store procedure goto ….etc like

  20. Hi Pinal

    I have restored SQL Server 2008 Database to Other New DB Server and In the new server – I want to create around 800 Orphaned windows users, Please let me know any short cut method to created Windows Login from the Users list of Restored database\Security\Users

    select u.name from master..syslogins l right join
    sysusers u on l.sid = u.sid
    where l.sid is null and issqlrole 1 and isapprole 1
    and (u.name ‘INFORMATION_SCHEMA’ and u.name ‘guest’
    and u.name ‘system_function_schema’)

  21. In Denali 2012 RTM Version, SP_HELPTEXT is creating extra newlines while generating DB Objects code which is very problematic. The difference I found in SP_Helptext of denali and older Version was as below –
    Old Versions –
    CREATE TABLE #CommentText
    (LineId int
    ,Text nvarchar(255) collate database_default)

    Denali Version –
    CREATE TABLE #CommentText
    (LineId int
    ,Text nvarchar(255) collate Catalog_default).

    Even we are not allowed to modify SP_HELPTEXT Sproc. Please help or suggest.

  22. Greetings: I am debugging some MS SQL 2K8r2 stored procedures and wanted to generate the line number of where I am catching various lines of code. Can you point me in the right direction?


    BTW: Love your site!

  23. hello sir………
    can you pleas tell me how image and text data can retrieve together from sql database….to a single page……

  24. by using this code u wl get code of trigger

    create procedure sp_view_trigger(@trig_name varchar(max),@trig_view text)
    select c.text ‘code of trigger’
    from sysobjects t inner join syscomments c
    on t.id=c.id
    where t.name=@trig_name

    declare @viewdata varchar(max)
    execute sp_view_trigger ‘name_of_trigger’ ,@viewdata
    print @viewdata

  25. can anyone explain it to me pls

    USE [bakundpur2013-14]
    /****** Object: Index [aaaaaStockAdjust_PK] Script Date: 07/24/2013 18:33:17 ******/
    [Shop_Code] ASC,
    [Bottle_Code] ASC,
    [StockDate] ASC

  26. –Show Stored Procedures
    select p.[type]
    from sys.objects p
    join sys.sql_modules c
    on p.object_id = c.object_id
    where p.[type] = ‘P’
    –and c.[definition] like ‘%foo%’
    ORDER BY p.[name]

    SELECT OBJECT_NAME(object_id) ProcedureName,
    FROM sys.sql_modules
    WHERE objectproperty(object_id,’IsProcedure’) = 1
    ORDER BY OBJECT_NAME(object_id)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s