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.

Syntax
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

64 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

    Like

  2. Hi,

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

    Excellent and precise information!

    Thanks.
    Gaurav

    Like

  3. 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.

    Like

  4. @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’ —
    or
    Execute as user = ‘new_login_name’

    –Example:
    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
    or
    select * from sysfiles — sql server 2000

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

    Select * from syslogins — sql server 2005
    or
    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
    or
    Select * from sys.all_objects — SQL Server 2005

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

    Select * from information_schema.tables
    or
    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
    or
    Exec Sp_Helptext [owner.objectname]

    /*================================================================================================
    Get Table ,View structure, information about indexes, constraints, data type, data length.
    ================================================================================================*/
    Exec Sp_Help object_name
    or
    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
    or
    sp_helprotect @user_name = ‘name of the user’

    Like

  5. @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
    ———————————————————–

    http://blog.sqlauthority.com/2007/01/20/sql-server-query-analyzer-shortcuts/

    http://blog.sqlauthority.com/2008/04/27/sql-server-query-analyzer-shortcuts-part-2/

    SQL Server 2005 – SQL Server Management Studio Short Cuts
    ——————————————————————————–

    http://blog.sqlauthority.com/2007/05/04/sql-server-download-sql-server-management-studio-keyboard-shortcuts-ssms-shortcuts/

    SQL Server Cheat Sheet (Very good one page information)
    —————————————————————————

    http://blog.sqlauthority.com/2009/05/20/sql-server-download-pdf-sql-server-cheat-sheet/

    Enjoy.

    ~ IM.

    Like

  6. 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

    Like

  7. 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?

    Thanks,
    mpv

    Like

  8. 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 ‘
    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE left(”?”,2) IN(”SL”,”GR”,”TR”)
    )
    BEGIN
    USE ?
    SELECT DB_NAME() AS ”?”,OBJECT_NAME(id),text
    FROM syscomments
    WHERE [text] LIKE ”%mysearchstring%” AND OBJECTPROPERTY(id, ”IsTrigger”) = 1
    END

    Like

  9. 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.

    Like

  10. 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.

    thanks

    selva

    Like

  11. 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)
    AS
    DECLARE @SQL NVARCHAR(MAX)
    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]

    as
    begin

    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

    end

    Like

  12. 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.

    Like

  13. 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

    Like

  14. 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

    Like

  15. ALTER VIEW [dbo].[vwProviderUtilization]
    AS
    SELECT
    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..

    Like

  16. 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

    Like

  17. 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’)

    Like

  18. 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.

    Like

  19. 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?

    Thanks!

    BTW: Love your site!

    Like

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

    Like

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

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

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

    Like

  22. can anyone explain it to me pls

    USE [bakundpur2013-14]
    GO
    /****** Object: Index [aaaaaStockAdjust_PK] Script Date: 07/24/2013 18:33:17 ******/
    ALTER TABLE [dbo].[StockAdjust] ADD CONSTRAINT [aaaaaStockAdjust_PK] PRIMARY KEY NONCLUSTERED
    (
    [Shop_Code] ASC,
    [Bottle_Code] ASC,
    [StockDate] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    Like

  23. –Show Stored Procedures
    select p.[type]
    ,p.[name]
    ,c.[definition]
    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,
    definition
    FROM sys.sql_modules
    WHERE objectproperty(object_id,’IsProcedure’) = 1
    ORDER BY OBJECT_NAME(object_id)

    Like

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