-
January 14th, 2009, 08:41 AM
#1
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
-
January 14th, 2009, 09:22 AM
#2
Re: find store procedure
In database inside the "syscomments" table ....
;-)
-
January 14th, 2009, 10:27 AM
#3
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? :-)
Originally Posted by Krzemo
In database inside the "syscomments" table ....
;-)
regards,
George
-
January 14th, 2009, 10:39 PM
#4
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.
-
January 14th, 2009, 11:57 PM
#5
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]'
Originally Posted by Krzemo
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
-
January 15th, 2009, 12:15 AM
#6
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
-
January 15th, 2009, 12:21 AM
#7
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. :-)
Originally Posted by Krzemo
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
-
January 15th, 2009, 12:33 AM
#8
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
-
January 15th, 2009, 12:59 AM
#9
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? :-)
Originally Posted by Krzemo
because it (user/schema id) is in different column.
;-)
sp_helptext splits up that formal name and do exactly the same select
regards,
George
-
January 15th, 2009, 01:07 AM
#10
Re: find store procedure
yes
-
January 15th, 2009, 01:11 AM
#11
Re: find store procedure
Cool, Krzemo! Question answered. :-)
Originally Posted by Krzemo
yes
regards,
George
-
March 26th, 2009, 12:30 AM
#12
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|