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 (https://blog.sqlauthority.com) , BOL

Solarwinds
, , ,
Previous Post
SQL SERVER – Disadvantages (Problems) of Triggers
Next Post
SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

Related Posts

71 Comments. Leave new

  • thanks very useful information

    Reply
  • 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

    Reply
    • Why do you want not to use a query analyser for this. It is easy to view definition of a stored procedure using query analyser

      Reply
  • How to view, all users’ name of a database, in ms sql server 7 ?

    Reply
  • 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’)

    Reply
  • how can i open procedure with perfect alignment in sql server using a command like sp_helptext

    Reply
  • Please tell me the query to view the definition of a table

    Reply
  • Hello Pinal Sir!
    How to check stored procedures with same scripts or output results?

    Reply
  • 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.

    Reply
  • Tony Bromirski
    June 28, 2012 7:14 am

    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!

    Reply
  • anshul agarwal
    July 23, 2012 3:06 pm

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

    Reply
  • thanks x 1000000!!

    Reply
  • 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

    Reply
  • 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]

    Reply
  • –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)

    Reply
  • Pinal you are amazing.. i found every thing in your blog

    Reply
  • Stéphane Bello
    November 3, 2015 3:25 pm

    This is what I wrote to get all procedures in separate SQL files in order to commit them in SVN and to follow changes made on them.
    But (yes), there is a misery about this: sometimes, for no identified reason, instead of returning ‘CREATE PROCEDURE [dbo].[procedure_name]’, sp_helptext is only returning ‘CREATE PROCEDURE procedure_name’
    When I use ‘script stored procedure as create to’, the script is containing ‘CREATE PROCEDURE [dbo].[procedure_name]’.
    This is the only problem I have to completely finish this.
    If someone is interested in:
    The stored procedure is called by a client that is creating one file for each stored procedure using ‘START_PRC’ and ‘END_PRC’ to find the starting and ending points of a procedure.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    — CREATED TO WRITE THE CODE IN SEPARATE SQL FILES

    CREATE procedure [dbo].[XXX_StoredProceduresInText]
    @ONLY_NAMES BIT
    AS

    set nocount on

    DECLARE @TEXT AS NVARCHAR(MAX)
    DECLARE @SCRIPT AS NVARCHAR(MAX)

    DECLARE @Cursor Cursor
    DECLARE @CursorTB Cursor
    DECLARE @PROC NVARCHAR(MAX)

    –N’sp_helptext ”[dbo].[‘ + DbObjects.Name + ‘]”;’

    Set @Cursor = CURSOR FOR
    SELECT
    m.uses_ansi_nulls, m.uses_database_collation, m.uses_quoted_identifier,
    N’sp_helptext ”’ + DbObjects.Name + ”’;’
    FROM sys.Objects DbObjects
    INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
    INNER JOIN sys.sql_modules m ON m.object_id = DbObjects.object_id
    WHERE SysSchemas.Name = ‘dbo’ and DbObjects.name not like ‘sp_%’
    and DbObjects.name not like ‘X%’ and
    DbObjects.name not like ‘dt_%’
    AND (DbObjects.Type in (‘P’, ‘FN’))
    order by DbObjects.Type DESC, DbObjects.Name

    DECLARE @TEMP TABLE (numero int IDENTITY(1,1), Xansi_nulls bit, Xdatabase_collation bit, Xquoted_identifier bit, OBJECT_NAME VARCHAR(500), SCRIPT NVARCHAR(MAX))
    DECLARE @SCRIPT_TB TABLE (numero int IDENTITY(1,1), SCRIPT NVARCHAR(MAX))
    DECLARE @ansi_nulls bit, @database_collation bit, @quoted_identifier bit

    OPEN @Cursor
    FETCH NEXT FROM
    @Cursor
    INTO
    @ansi_nulls, @database_collation, @quoted_identifier, @TEXT
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DELETE FROM @SCRIPT_TB
    INSERT INTO @SCRIPT_TB EXEC(@TEXT)
    IF(CHARINDEX(‘–END_PRC–‘, @TEXT) = 0)
    INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(‘–END_PRC–‘)

    Set @CursorTB = CURSOR FOR
    SELECT SCRIPT FROM @SCRIPT_TB

    OPEN @CursorTB
    FETCH NEXT FROM
    @CursorTB
    INTO
    @PROC
    WHILE @@FETCH_STATUS = 0
    BEGIN
    INSERT INTO
    @TEMP
    (Xansi_nulls, Xdatabase_collation, Xquoted_identifier, SCRIPT)
    VALUES( @ansi_nulls, @database_collation, @quoted_identifier, @PROC)
    FETCH NEXT FROM
    @CursorTB
    INTO
    @PROC
    END

    DEALLOCATE @CursorTB

    FETCH NEXT FROM @Cursor INTO
    @ansi_nulls, @database_collation, @quoted_identifier, @TEXT

    END

    DELETE FROM @SCRIPT_TB

    DEALLOCATE @CURSOR

    –+ CHAR(13) + CHAR(10)

    IF(@ONLY_NAMES = 0)
    Set @Cursor = CURSOR FOR
    SELECT T.Xansi_nulls, T.Xdatabase_collation, T.Xquoted_identifier, T.SCRIPT
    FROM @TEMP T ORDER BY numero
    ELSE
    Set @Cursor = CURSOR FOR
    SELECT T.Xansi_nulls, T.Xdatabase_collation, T.Xquoted_identifier, T.SCRIPT
    FROM @TEMP T
    WHERE
    CHARINDEX(‘CREATE ‘, T.SCRIPT, 0) > 0 AND
    (
    CHARINDEX(‘ PROC ‘, T.SCRIPT, 0) > 0 OR
    CHARINDEX(‘ PROCEDURE ‘, T.SCRIPT, 0) > 0 OR
    CHARINDEX(‘ FUNCTION ‘, T.SCRIPT, 0) > 0
    )
    ORDER BY numero

    DECLARE @ANSI VARCHAR(100)
    DECLARE @DB_COLLATION VARCHAR(100)
    DECLARE @QUOTED VARCHAR(100)

    OPEN @Cursor
    FETCH NEXT FROM
    @Cursor
    INTO
    @ansi_nulls, @database_collation, @quoted_identifier, @TEXT
    WHILE @@FETCH_STATUS = 0
    BEGIN

    SET @ANSI = ”
    SET @QUOTED = ”
    SET @DB_COLLATION = ”

    IF
    (
    @ONLY_NAMES = 0 AND
    (CHARINDEX(‘CREATE ‘, @TEXT, 0) > 0 AND
    (
    CHARINDEX(‘ PROC ‘, @TEXT, 0) > 0 OR
    CHARINDEX(‘ PROCEDURE ‘, @TEXT, 0) > 0 OR
    CHARINDEX(‘ FUNCTION ‘, @TEXT, 0) > 0
    ))
    )
    BEGIN
    –THIS IS A NEW PROCEDURE
    IF(@ansi_nulls > 0)
    BEGIN
    SET @ANSI = ‘SET ANSI_NULLS ON’
    END
    ELSE
    BEGIN
    SET @ANSI = ‘SET ANSI_NULLS OFF’
    END
    IF(@quoted_identifier > 0)
    BEGIN
    SET @QUOTED = ‘SET QUOTED_IDENTIFIER ON’
    END
    ELSE
    BEGIN
    SET @QUOTED = ‘SET QUOTED_IDENTIFIER OFF’
    END

    IF(CHARINDEX(‘–START_PRC–‘, @TEXT) = 0)
    INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(‘–START_PRC–‘)
    INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(@ANSI)
    INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(‘GO’)
    INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(@QUOTED)
    INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(‘GO’)
    END — OF NEW FUNCTION OR PROCEDURE

    INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(@TEXT)

    FETCH NEXT FROM
    @Cursor
    INTO
    @ansi_nulls, @database_collation, @quoted_identifier, @TEXT
    END

    DEALLOCATE @Cursor

    select S.SCRIPT from @SCRIPT_TB S
    order by S.numero

    Reply
  • Thanks a lot, for being consistent and very helpful instead of fill out all the page with scumware

    Reply
  • Hello Sir i have one *.mdf file database but dont have his LDF File so it can not attach in my laptop.
    is that any way to attach or see his tables and store procedure without SQl Server.

    Thanks
    Montu

    Reply
  • Hello sir,

    I have 10 databases in a particular SQL server. I am using stored procedure. I want to get the content of a particular stored procedure, don’t want to search one by one. is there a query which would search in all the database and show me the results ??

    Reply
  • can we change table name and it will automatically change in stored procedures and views both
    ????????????????????/

    Reply

Leave a Reply

Menu