CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 12 of 12
  1. #1
    George2 is offline Elite Member Power Poster
    Join Date
    Oct 2002
    Posts
    4,468

    find store procedure

    Hello everyone,

    Any ideas to find where is the physical location of store procedures showed under Programmability --> Store Procedures of Management Studio?

    (sorry for my naive question again) :-)

    1.

    I have a source control system and I want to check-in the store procedure code (I think they should be in the form of .sql) so that all developers could work on them in a synchronous way?

    2.

    And I also want to merge the store procedure code outside Management Studio using other diff tools and let Management Studio reflect the changes -- e.g. if I merge/change the code from outside, when I press refresh button in Management Studio, I can see the new merged code of store procedures.

    thanks in advance,
    George

  2. #2
    Join Date
    Nov 2004
    Location
    Poland
    Posts
    1,355

    Re: find store procedure

    In database inside the "syscomments" table ....
    ;-)

  3. #3
    George2 is offline Elite Member Power Poster
    Join Date
    Oct 2002
    Posts
    4,468

    Re: find store procedure

    Sorry, Krzemo. My experience is limited on this point. Could you let me know the database name from which there is syscomments table please? :-)

    Quote Originally Posted by Krzemo View Post
    In database inside the "syscomments" table ....
    ;-)
    regards,
    George

  4. #4
    Join Date
    Nov 2004
    Location
    Poland
    Posts
    1,355

    Re: find store procedure

    Could you let me know the database name from which there is syscomments table please? :-)
    It is in Your database... I don't know its name ...
    ;-)

    Try this:
    Code:
    SELECT c.text 
     FROM sysobjects o
     INNER JOIN syscomments c ON c.id=o.id
     WHERE o.name='YoursProcedureName'
    ORDER BY colid
    OR this:
    Code:
    EXEC sp_helptext 'YoursProcedureName'
    ;-)

    Best regards,
    Krzemo.

  5. #5
    George2 is offline Elite Member Power Poster
    Join Date
    Oct 2002
    Posts
    4,468

    Re: find store procedure

    Thanks Krzemo,

    I have tried both methods.

    1. Any solutions to get all names of all user defined (other than system built-in) store procedure?

    2. The 1st solution does not work (returning result is empty), but the 2nd works. Here is my code for both. Any ideas why the 1st returns empty result?

    Code:
    SELECT c.text 
     FROM sysobjects o
     INNER JOIN syscomments c ON c.id=o.id
     WHERE o.name='[dbo].[prc_AddABC]'
    ORDER BY colid
    
    Exec sp_helptext '[dbo].[prc_AddABC]'
    Quote Originally Posted by Krzemo View Post
    It is in Your database... I don't know its name ...
    ;-)

    Try this:
    Code:
    SELECT c.text 
     FROM sysobjects o
     INNER JOIN syscomments c ON c.id=o.id
     WHERE o.name='YoursProcedureName'
    ORDER BY colid
    OR this:
    Code:
    EXEC sp_helptext 'YoursProcedureName'
    ;-)

    Best regards,
    Krzemo.
    regards,
    George

  6. #6
    Join Date
    Nov 2004
    Location
    Poland
    Posts
    1,355

    Re: find store procedure

    should be:
    Code:
     
    SELECT c.text 
    FROM sysobjects o
    INNER JOIN syscomments c ON c.id=o.id
    WHERE o.name='prc_AddABC'
    ORDER BY colid

  7. #7
    George2 is offline Elite Member Power Poster
    Join Date
    Oct 2002
    Posts
    4,468

    Re: find store procedure

    Thanks Krzemo,

    Your new solution below works. But why I can not use [dbo].[prc_AddABC]? It is more formal name than the short name prc_AddABC. :-)

    Quote Originally Posted by Krzemo View Post
    should be:
    Code:
     
    SELECT c.text 
    FROM sysobjects o
    INNER JOIN syscomments c ON c.id=o.id
    WHERE o.name='prc_AddABC'
    ORDER BY colid
    regards,
    George

  8. #8
    Join Date
    Nov 2004
    Location
    Poland
    Posts
    1,355

    Re: find store procedure

    because it (user/schema id) is in different column.
    ;-)

    sp_helptext splits up that formal name and do exactly the same select

  9. #9
    George2 is offline Elite Member Power Poster
    Join Date
    Oct 2002
    Posts
    4,468

    Re: find store procedure

    Thanks Krzemo,

    You mean sysobjects.name stores exactly the store procedure name without schema name? And the select statement does exact match? :-)

    Quote Originally Posted by Krzemo View Post
    because it (user/schema id) is in different column.
    ;-)

    sp_helptext splits up that formal name and do exactly the same select
    regards,
    George

  10. #10
    Join Date
    Nov 2004
    Location
    Poland
    Posts
    1,355

    Re: find store procedure

    yes

  11. #11
    George2 is offline Elite Member Power Poster
    Join Date
    Oct 2002
    Posts
    4,468

    Re: find store procedure

    Cool, Krzemo! Question answered. :-)

    Quote Originally Posted by Krzemo View Post
    yes
    regards,
    George

  12. #12
    Join Date
    Feb 2008
    Location
    Bangalore
    Posts
    149

    Re: find store procedure

    If MSSQL Sever is the Database server Then

    SysObejcts is the table you should look for any objects exists in the data base.

    For Example
    Select * from Sysobjects where Xtype='U'
    Returns All the user created table details.

    Xtype filed Summary

    TR ---> Trigger
    U ----> User created Table
    C -----> Check Constraint
    S -----> System Tables
    D -----> Default Value
    PK ---> Primary Key
    FN ----> User Defined Functions
    TF -----> User defined Function returning Table type of Data
    V -----> Views
    P -----> Stored Procedure

    From Sysobjects table you can get id of the objects and from other system tables
    like syscolumns,syscomments... etc you can the entire details of required objects
    Encourage the efforts of fellow members by rating

    Lets not Spoon Feed and create pool of lazy programmers

    - ComIT Solutions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured