SP

Question

Post reply

SP

niranjankumar_k

Ten Centuries

Points: 1075
More actions
May 6, 2008 at 4:02 am

#186106

Hi ,
i moved to sql 2005 from sql 2000. Stored procedure should be in synch with both server.if i take from sysobject it shows all stored procedures. so How to find system defined stored procedure , user defined stored procedure in sql 2005 and sql 2000.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SqlUser-529296 SSCarpal Tunnel Points: 4072 More actions · Answer 1

Hi niranjankumar,

All system Procedure's should start with sys.sp_.

And all the user defined procedure you want to find out by using the following stmts.

SELECT * FROM sys.sysobjects

WHERE [name] LIKE '%SP%'

AND xtype LIKE 'P'

----

Bert-687390 SSC Eights! Points: 847 More actions · Answer 2

sqluser (5/6/2008)
And all the user defined procedure you want to find out by using the following stmts.
SELECT * FROM sys.sysobjects
WHERE [name] LIKE '%SP%'
AND xtype LIKE 'P'

Many SQL users do not prefix their own stored procedures with 'SP', only the xtype LIKE 'P' will return the correct result too. Using xtype='S' will return the system stored procedures.

[font="Courier New"]
------------------------
MCPD Web Developer, MCPD Windows Developer[/font]

Computers are made to solve problems you did not have in the days they didn't exist.

gan-685682 Ten Centuries Points: 1136 More actions · Answer 3

bert (5/6/2008)
sqluser (5/6/2008)
And all the user defined procedure you want to find out by using the following stmts.
SELECT * FROM sys.sysobjects
WHERE [name] LIKE '%SP%'
AND xtype LIKE 'P'
Many SQL users do not prefix their own stored procedures with 'SP', only the xtype LIKE 'P' will return the correct result too. Using xtype='S' will return the system stored procedures.

I think xtype='S' is system tables??