none
sp_helptext give wrong store procedure RRS feed

  • Question

  • I tried to get a store procedure with sp_helptext.
    In result it gives me almost same name another store procedure. That suffers me a lot. Is That a Bug?
    Monday, December 9, 2019 7:52 AM

All replies

  • I cannot reproduce it, what version you are using?

    Try (specify schema)

    EXEC sp_helptext 'your_schema.your_procname'

    SELECT OBJECT_DEFINITION(OBJECT_ID('yourprocname')) 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 9, 2019 7:57 AM
    Answerer
  • This usually happens if someone uses sp_rename to rename stored Procedure from X_abcd to X at some point.
    Monday, December 9, 2019 8:01 AM
  • I am Using SSMS 2014 (version 12.0.2000.8)

    In Query window    EXEC sp_helptext Smt_Mc_Schedule_Maintenance_Load_Completed

    In Results 

    CREATE PROCEDURE [dbo].[Smt_Mc_Service_Maintenance_Load_Completed]

    @User varchar(20),
    @SCompany int

    AS
    BEGIN

    .............................

    ..............................

    END

    This is not the store procedure I searched.
    Monday, December 9, 2019 8:21 AM
  • If you try this way?

    EXEC sp_helptext 'dbo.Smt_Mc_Schedule_Maintenance_Load_Completed'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, December 9, 2019 8:30 AM
    Answerer
  • BTW you can open sp_helptext system stored procedure and debug if you want to understand what is going on

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 9, 2019 8:31 AM
    Answerer
  • I also tried that. same problem.

    Monday, December 9, 2019 8:54 AM
  • If so you use another technique

    use  dbname

    go

    SELECT definition  
    FROM sys.sql_modules  
    WHERE object_id = (OBJECT_ID(N'dbname.dbo.yourSP'));  


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 9, 2019 9:05 AM
    Answerer
  • Thank you for your replay.

    I found that problem. I don't understand how that kind of problem happen.

    In [sys].[sysobjects]  Name and Id is different

                         id                                          name 
    121103522                  Smt_Mc_Schedule_Maintenance_Load_Completed           
    185103750                  Smt_Mc_Service_Maintenance_Load_Completed              

    But  in [sys].[syscomments] 

         id                                                                                          text

    121103522                                 CREATE PROCEDURE [dbo].[Smt_Mc_Service_Maintenance_Load_Completed]...................
    185103750                                 CREATE PROCEDURE [dbo].[Smt_Mc_Service_Maintenance_Load_Completed]...................

    id is different but Procedure name are same. Still I'm surprised.





    • Edited by RezaurRahman Monday, December 9, 2019 10:18 AM
    • Marked as answer by RezaurRahman Monday, December 9, 2019 10:19 AM
    • Unmarked as answer by RezaurRahman Monday, December 9, 2019 10:21 AM
    Monday, December 9, 2019 10:16 AM
  • Do not rely on syscomments system tables, why not using the script I posted above 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, December 9, 2019 10:22 AM
    Answerer
  • I checked that already. In [sql_modules] also  object_id is different but definition is same.
    Monday, December 9, 2019 10:29 AM
  • Adding to the answer by @VSInfinity, this is an artifact of renaming textual objects (procs, triggers, functions). sp_rename updates the name in the catalog but the original text remains unchanged.

    If you script the object using SSMS/SMO, the tool will generate the CREATE/ALTER/DROP statement with the proper name.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, December 9, 2019 10:50 AM
  • If the stored proc was renamed after it was created, the text in helptext will NOT reflect the new name.


    Monday, December 9, 2019 12:12 PM
    Moderator
  • Hi RezaurRahman, 

    I do some test and then I reproduce your issue . You might use sp_rename in your script .  Please refer to sp_rename (Transact-SQL). If you'd use a T_SQL command instead of GUI (all that you "clicks" in Management Studio produces T-SQL coede behind the scenes), you'd get a warning as shown in my code.

     1.Caution:(in link sp_rename (Transact-SQL))

    Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

    2.Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object either in the definition column of the sys.sql_modules catalog view or obtained using the OBJECT_DEFINITION built-in function. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.

    So when you want to rename your procedure you should script your procedure as CREATE and change the procedure name, doing so you recreate your proc with the new name and then you can drop the old one.

    Following script will show you the issue clearly .Please check . 

    create table test(ID int )
    go
    create procedure Smt_Mc_Service_Maintenance_Load_Completed as 
    begin 
    select * from test 
    end 
    go
    EXEC sp_rename 'Smt_Mc_Service_Maintenance_Load_Completed', 'Smt_Mc_Schedule_Maintenance_Load_Completed';
    go 
    /*
    Caution: Changing any part of an object name could break scripts and stored procedures.
    */
    create procedure Smt_Mc_Service_Maintenance_Load_Completed as 
    begin 
    select * from test 
    end 
    go 
    select id , name  from [sys].[sysobjects] where type='P'
    select id, text from [sys].[syscomments]
    /*
    id          name
    ----------- ---------------------------------------------------------
    917578307   Smt_Mc_Schedule_Maintenance_Load_Completed
    933578364   Smt_Mc_Service_Maintenance_Load_Completed
    
    
    id          text
    ----------- --------------------------------------------------------------------------------
    917578307   create procedure Smt_Mc_Service_Maintenance_Load_Completed as 
    			begin 
    			select * from test 
    			end 
    933578364   create procedure Smt_Mc_Service_Maintenance_Load_Completed as 
    			begin 
    			select * from test 
    			end 
    */
    
    EXEC sp_helptext 'Smt_Mc_Schedule_Maintenance_Load_Completed'
    /*
    Text
    --------------------------------------------------------------------
    create procedure Smt_Mc_Service_Maintenance_Load_Completed as 
    begin 
    select * from test 
    end 
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 10, 2019 7:15 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 20, 2019 7:26 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 26, 2019 3:04 AM