Hello,
What table can you use to get a list of all of the stored procedures in a
database? I need to be able to delete the ones created by users. I am
assuming that I will need to use a cursor to step through and delete them.
I created one for tables as an example (see below).
--
Thanks in advance,
sck10
USE MyDatabase
DECLARE @.strCursor varchar(255), @.strExe varchar(255)
-- Declare Cursor Batch
DECLARE csrPKey CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_NAME LIKE 'tbl%') OR (TABLE_NAME LIKE 'tlkp%')
ORDER BY TABLE_NAME
OPEN csrPKey
FETCH NEXT FROM csrPKey INTO @.strCursor
WHILE @.@.FETCH_STATUS = 0 BEGIN -- 0 = success, -1 = outside recordset, -2
= row no longer exist
SELECT @.strCursor
SET @.strExe = 'DROP TABLE ' + @.strCursor
EXECUTE (@.strExe)
FETCH NEXT FROM csrPKey INTO @.strCursor
END
CLOSE csrPKey
DEALLOCATE csrPKeyThis is a multi-part message in MIME format.
--000808040309040806010702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
You can do a similar thing with the INFORMATION_SCHEMA.ROUTINES view
(this would be the Microsoft approved, kosher way) or the dbo.sysobjects
table in the database (which is how I'd probably do it - old habits die
hard, but I'm trying to be good and start using the new catalog views &
DMVs).
--
*mike hodgson*
http://sqlnerd.blogspot.com
sck10 wrote:
>Hello,
>What table can you use to get a list of all of the stored procedures in a
>database? I need to be able to delete the ones created by users. I am
>assuming that I will need to use a cursor to step through and delete them.
>I created one for tables as an example (see below).
>
--000808040309040806010702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can do a similar thing with the INFORMATION_SCHEMA.ROUTINES
view (this would be the Microsoft approved, kosher way) or the
dbo.sysobjects table in the database (which is how I'd probably do it -
old habits die hard, but I'm trying to be good and start using the new
catalog views & DMVs).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
sck10 wrote:
<blockquote cite="miduwetxFQhGHA.4080@.TK2MSFTNGP03.phx.gbl" type="cite">
<pre wrap="">Hello,
What table can you use to get a list of all of the stored procedures in a
database? I need to be able to delete the ones created by users. I am
assuming that I will need to use a cursor to step through and delete them.
I created one for tables as an example (see below).
</pre>
</blockquote>
</body>
</html>
--000808040309040806010702--|||Hello Sck10,
As for the database server, is it a SQL Server 2000 instance or SQL Server
2005 instance? As for SQL Server 2005, you can use the "sys.procedures"
catalog view to query all the user defined store procedures in a certain
database.
#sys.procedures (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188737.aspx
e.g. select * from sys.procedures
For sqlserver 2000, there is no such system catalog view, however, we can
query the "sysobjects" system table.(Also works for SQL Server 2005) e.g:
select * from sysobjects where type='P' order by name asc
This will return all the storeprocedures available in the current database
context(included system sp), you can filtering through the "category" field
in this system table to query user defined sp only.
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||This is a multi-part message in MIME format.
--090903010301050008030801
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I'm guessing if the OP is using the INFORMATION_SCHEMA views rather than
the sys.* views then he's probably working with SQL 2000. As for SQL
2000, what's wrong with the INFORMATION_SCHEMA.ROUTINES view? I thought
Microsoft advised people to use the INFORMATION_SCHEMA views rather than
the system tables such as dbo.sysobjects.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Steven Cheng[MSFT] wrote:
>Hello Sck10,
>As for the database server, is it a SQL Server 2000 instance or SQL Server
>2005 instance? As for SQL Server 2005, you can use the "sys.procedures"
>catalog view to query all the user defined store procedures in a certain
>database.
>#sys.procedures (Transact-SQL)
>http://msdn2.microsoft.com/en-us/library/ms188737.aspx
>e.g. select * from sys.procedures
>For sqlserver 2000, there is no such system catalog view, however, we can
>query the "sysobjects" system table.(Also works for SQL Server 2005) e.g:
>select * from sysobjects where type='P' order by name asc
>This will return all the storeprocedures available in the current database
>context(included system sp), you can filtering through the "category" field
>in this system table to query user defined sp only.
>Hope this helps.
>Regards,
>Steven Cheng
>Microsoft Online Community Support
>
>==================================================>When responding to posts, please "Reply to Group" via your newsreader so
>that others may learn and benefit from your issue.
>==================================================>
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>Get Secure! www.microsoft.com/security
>(This posting is provided "AS IS", with no warranties, and confers no
>rights.)
>
>
--090903010301050008030801
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'm guessing if the OP is using the INFORMATION_SCHEMA views rather
than the sys.* views then he's probably working with SQL 2000. As for
SQL 2000, what's wrong with the INFORMATION_SCHEMA.ROUTINES view? I
thought Microsoft advised people to use the INFORMATION_SCHEMA views
rather than the system tables such as dbo.sysobjects.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Steven Cheng[MSFT] wrote:
<blockquote cite="midQEGhq6ThGHA.4948@.TK2MSFTNGXA01.phx.gbl" type="cite">
<pre wrap="">Hello Sck10,
As for the database server, is it a SQL Server 2000 instance or SQL Server
2005 instance? As for SQL Server 2005, you can use the "sys.procedures"
catalog view to query all the user defined store procedures in a certain
database.
#sys.procedures (Transact-SQL)
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn2.microsoft.com/en-us/library/ms188737.aspx</a>">http://msdn2.microsoft.com/en-us/library/ms188737.aspx">http://msdn2.microsoft.com/en-us/library/ms188737.aspx</a>
e.g. select * from sys.procedures
For sqlserver 2000, there is no such system catalog view, however, we can
query the "sysobjects" system table.(Also works for SQL Server 2005) e.g:
select * from sysobjects where type='P' order by name asc
This will return all the storeprocedures available in the current database
context(included system sp), you can filtering through the "category" field
in this system table to query user defined sp only.
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.microsoft.com/security</a>">http://www.microsoft.com/security">www.microsoft.com/security</a>
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
</pre>
</blockquote>
</body>
</html>
--090903010301050008030801--|||Thanks for your suggestion Mike,
I agree that the INFORMATION_SCHEMA.ROUTINES is also a good approach.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment