system procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

system procedures

Author

Topic

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-02 : 06:47:03

Hi all

as like there is a query in oracle as
select * from tab

we get all tables

Like wise is their any query in sql server 2005
b'coz

I want all system procedures
as like sp_password or sp_help
i want list of all procedures

for that i wrote query as

select * from sysobjects
but in output i don't find any name starting with sp_

Thanks in advance

Malathi Rao

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 06:49:40

Select * From Sys.objects and on the basis of the Type, you can filter out whether objects is a table, or sp or trigger.. etc..

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 06:57:20

Or you can use OBJECTPROPERTY() function along with IsMSShipped property.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-02 : 07:08:03

select * from master..sysobjects where xtype = 'P'

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-02 : 07:25:10

quote:
Originally posted by chiragkhabaria

Select * From Sys.objects and on the basis of the Type, you can filter out whether objects is a table, or sp or trigger.. etc..

Chirag

http://chirikworld.blogspot.com/

sorry to say i did'nt get the answer

Malathi Rao

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-02 : 07:28:26

quote:
Originally posted by pbguy

select * from master..sysobjects where xtype = 'P'

Thanks i got answer
but here in this query what is xtype

Malathi Rao

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 07:29:54

Try this:

Select * from Sys.Objects where type = 'P' and is_ms_shipped = 1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 07:31:20

quote:
Originally posted by jogin malathi

quote:
Originally posted by chiragkhabaria

Select * From Sys.objects and on the basis of the Type, you can filter out whether objects is a table, or sp or trigger.. etc..

Chirag

http://chirikworld.blogspot.com/

sorry to say i did'nt get the answer

Malathi Rao

When you do

 Select * From Sys.Objects

you will get the list of recordset containing all the database objects in that particular database.
and when applying the filter on the column Type, you can find a particular database object.

For instance you require to find out all the stored procedure in your datbase, then query will look like this


Select * From Sys.objects where [type]= 'P'

The list of other Types you can find in the book online under the topic sys.object

Chirag

http://chirikworld.blogspot.com/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 07:34:14

quote:
Originally posted by pbguy

select * from master..sysobjects where xtype = 'P'

This not correct, this will give the list of the stored procedure only in the database Master and not of the current database.

In SQL SERVER 2005, you have to use Sys.objects for getting information about the database objects.

Chirag

http://chirikworld.blogspot.com/

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-02 : 07:34:42

hi
i found the procedure to take database backup as

CREATE Procedure Backup_Database
(
@dbName varchar(100),
@Path varchar(200) = '' OUTPUT
)
As
Begin
Declare @Now varchar(100)

if Isnull(@dbName,'') = ''
Begin
RaisError('No Database Name specified while running the procedure',16,1)
return
End
if isnull(@path,'') = ''
Begin
RaisError('No file Name specified while running the procedure',16,1)
return
End
-- Make the filename
SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')
Select @path = @Path + '\' + @dbName + '_' + @Now + '.Bak'
-- Take the backup of the database at the specified folder
Backup Database @dbName
To Disk = @Path
With Init
if @@Error <> 0
Begin
RaisError('Error Occurred while taking the Backup, Please check Error Log for Details',16,1)
return
End

Print 'Backup Taken Successfully at ' + @path
-- RETURN @path
End

GO

and iam executing procs as follows

exec Backup_Database 'Malathi','C:/New Folder (2)'

above executaion gives error as follows

Cannot open backup device 'C:\New Folder (2)\Malathi_20070502170331.Bak'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.
Error Occurred while taking the Backup, Please check Error Log for Details

Malathi Rao

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 07:37:49

Is this anything related to your previous post??? if not then please make a new thread for the new questions..

Chirag

http://chirikworld.blogspot.com/

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-02 : 08:01:36

quote:
Originally posted by chiragkhabaria

Is this anything related to your previous post??? if not then please make a new thread for the new questions..

Chirag

http://chirikworld.blogspot.com/

how can i find parameters for particular procedure
is their any query for this
Malathi Rao

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-02 : 08:07:24

You have to take from the syscomments table's text column for the corresponding id what are the arguments present between '(' and ')' paranthesis.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 08:07:51

[code]
Select PARAMETER_NAME From Information_schema.PARAMETERS Where SPECIFIC_NAME = 'YourProcedureName'
--EDIT
And IS_RESULT = 'NO'
[/code]

Chirag

http://chirikworld.blogspot.com/

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-02 : 08:08:57

quote:
Originally posted by pbguy

You have to take from the syscomments table's text column for the corresponding id what are the arguments present between '(' and ')' paranthesis.

Sorry i did'nt get you

Malathi Rao

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-02 : 08:15:47

cool chirag.

select * from master.information_schema.parameters where specific_name = 'sp_helptext'
and is_result = 'NO'

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-02 : 08:40:01

quote:
Originally posted by pbguy

cool chirag.

select * from master.information_schema.parameters where specific_name = 'sp_helptext'
and is_result = 'NO'

how to search a procedure location
like database name or servername

Malathi Rao

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 08:46:58

Have you looked carefully at any of the queries posted?

If you would have modified Chirag's last query to list all columns instead of specific one, you could easily have spotted SPECIFIC_CATALOG column there in the output which denotes database to which object belongs.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-02 : 09:04:07

quote:
Originally posted by harsh_athalye

Have you looked carefully at any of the queries posted?

If you would have modified Chirag's last query to list all columns instead of specific one, you could easily have spotted SPECIFIC_CATALOG column there in the output which denotes database to which object belongs.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ya i checked Chirag's last query
but this gives SPECIFIC_CATALOG of present database

but i want all the SPECIFIC_CATALOG (ie) database names in the server

Malathi Rao

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-02 : 09:10:48

quote:
Originally posted by jogin malathi

quote:
Originally posted by harsh_athalye

Have you looked carefully at any of the queries posted?

If you would have modified Chirag's last query to list all columns instead of specific one, you could easily have spotted SPECIFIC_CATALOG column there in the output which denotes database to which object belongs.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ya i checked Chirag's last query
but this gives SPECIFIC_CATALOG of present database

but i want all the SPECIFIC_CATALOG (ie) database names in the server

Malathi Rao

i hav one procedure as malathi
for finding parameters i need to specifty database.information_schema.parameters
for this first i should know the database name

for this reason i want the database name where procdure is

Malathi Rao

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 09:14:27

Do you mean you want to search for specific procedure across the databases?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources