Listing procdure/trigger SQL code

I would like to create a script that lists out all of the SQL code for the procedures and triggers I have created in a database.

What I want to do is save the code for each procedure/trigger as a *.SQL file without having to open up each one in Enterprise Manager and save them individually.

Listing the names is easy by looking in sysobjects, but is the SQL code stored in a system table anywhere?

Does anybody have any alternative approach to this problem?

Thanks in advance,

MarkI don't understand you problem. EM allows you to script out all the objects of any particular type(s) to separate files. There is nothing that limits you to one object at a time.

Listing permissions on Stored Procedures via query.

How do I list the permissions on a stored procedure with a query?
I'm trying to remove Public from some xp_* procedures but I'd like to see
what Public is assigned to first.
Thanks much.Look up the system procedure sp_helprotect in SQL Server Books Online.
Anith|||"Horst" <Horst@.discussions.microsoft.com> wrote in message
news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> How do I list the permissions on a stored procedure with a query?
> I'm trying to remove Public from some xp_* procedures but I'd like to see
> what Public is assigned to first.
>
> Thanks much.
You could reverse engineer the sp_helprotect sproc, but this should work
for you as well.
--
USE master
GO
CREATE TABLE #Foo (
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType varchar(50),
Action varchar(50),
[Column] sysname NULL
)
INSERT #Foo
EXEC sp_helprotect
SELECT *
FROM #Foo
WHERE OBJECT LIKE 'xp%'
AND Grantee = 'public'
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the help.
"Rick Sawtell" wrote:

> "Horst" <Horst@.discussions.microsoft.com> wrote in message
> news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> You could reverse engineer the sp_helprotect sproc, but this should work
> for you as well.
> --
> USE master
> GO
>
> CREATE TABLE #Foo (
> Owner sysname,
> Object sysname,
> Grantee sysname,
> Grantor sysname,
> ProtectType varchar(50),
> Action varchar(50),
> [Column] sysname NULL
> )
> INSERT #Foo
> EXEC sp_helprotect
> SELECT *
> FROM #Foo
> WHERE OBJECT LIKE 'xp%'
> AND Grantee = 'public'
> DROP TABLE #Foo
> --
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Thanks for the help.
"Anith Sen" wrote:

> Look up the system procedure sp_helprotect in SQL Server Books Online.
> --
> Anith
>
>

Listing of all Stored Procedures

Where and how can I list all stored procedures listed in a specific database?
Thank you.
Hello,
The below system procedure can be used to list all procedures in a database
sp_stored_procedures
Thanks
Hari
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5D259ABB-FC93-4467-BFE1-082039AD26BE@.microsoft.com...
> Where and how can I list all stored procedures listed in a specific
> database?
> Thank you.
|||Thank you very much!
"Hugo Kornelis" wrote:

> On Wed, 3 Jan 2007 08:16:00 -0800, Terry wrote:
>
> Hi Terry,
> For SQL Server 2005:
> SELECT name
> FROM sys.procedures;
> For SQL Server 2000:
> SELECT name
> FROM sysobjects
> WHERE type = 'P'
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>

listing columns in view and stored procedures

Using SS2000 SP4. If I create a view and list the columns in the select
statement, if I then create a sp using the view is it better to list the
columns again or just use '*'?
Thanks,
--
Dan D.Do not use *. You will not know what you are selecting if your view
definition is changed.
"Dan D." wrote:

> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||You will get varying opinions here, especially since we don't know what your
definition of "better" is.
My opinion:
*ALWAYS* list your columns in production code, and never use SELECT *.
Too many things can go wrong throughout the pipeline.
A
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Good point. Thanks.
--
Dan D.
"Omnibuzz" wrote:
> Do not use *. You will not know what you are selecting if your view
> definition is changed.
> --
>
>
> "Dan D." wrote:
>|||That's how I feel. I saw a piece of code that was written using '*' and
wondered what other people thought.
Thanks,
--
Dan D.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You will get varying opinions here, especially since we don't know what yo
ur
> definition of "better" is.
> My opinion:
> *ALWAYS* list your columns in production code, and never use SELECT *.
> Too many things can go wrong throughout the pipeline.
> A
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||The person that wrote this should be shackled and whipped.
Since this is probably illegal in most states, provinces and countries...
He should at least be forced to write 100 times on a blackboard:
I shall never use "Select *" in production code again.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
> That's how I feel. I saw a piece of code that was written using '*' and
> wondered what other people thought.
> Thanks,
> --
> Dan D.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||I'll see if I can track him/her down.:)
--
Dan D.
"Raymond D'Anjou" wrote:

> The person that wrote this should be shackled and whipped.
> Since this is probably illegal in most states, provinces and countries...
> He should at least be forced to write 100 times on a blackboard:
> I shall never use "Select *" in production code again.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
>
>|||To add to the topic.
We now have a new company standard.. that you list ALL FIELDS in your INSERT
statements.
For Example:
at one point , an Emp table has EmpID, LastName, FirstName columns.
We had code like this
INSERT INTO Emp Values (101, 'Smith', 'John')
...
Why is this bad'
Someone adds a new column
Emp.Age.
Now every INSERT fails. Because the table has 4 columns, and the INSERT
supplies 3.
..
I can't tell you how many bugs I've tracked down with that (stupid) issue.
ALWAYS use a list. If it wasn't for quick debugging , Select * should be
outlawed! (Maybe a little extreme, but it can cause alot of issues)
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Thanks for your 2cents Sloan.
--
Dan D.
"sloan" wrote:

> To add to the topic.
> We now have a new company standard.. that you list ALL FIELDS in your INSE
RT
> statements.
> For Example:
> at one point , an Emp table has EmpID, LastName, FirstName columns.
> We had code like this
> INSERT INTO Emp Values (101, 'Smith', 'John')
> ...
> Why is this bad'
> Someone adds a new column
> Emp.Age.
> Now every INSERT fails. Because the table has 4 columns, and the INSERT
> supplies 3.
> ...
> I can't tell you how many bugs I've tracked down with that (stupid) issue.
> ALWAYS use a list. If it wasn't for quick debugging , Select * should be
> outlawed! (Maybe a little extreme, but it can cause alot of issues)
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||On Mon, 1 May 2006 10:23:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>Using SS2000 SP4. If I create a view and list the columns in the select
>statement, if I then create a sp using the view is it better to list the
>columns again or just use '*'?
>Thanks,
Aaron promised you varying opinions, but everyone was taking the same
side, so I figure it was time to add my two cents.
I consider * to be a very valuable tool in the SELECT list, and
prefer it in many situations. In general I prefer * when the query
MUST include EVERY column from a table. It avoids the possible error
of leaving a column out, and it enforces a uniform sequence to the
columns that can't hurt.
Example: A view that has to include every column from a table, plus
other columns:
SELECT X.*, Y.SomeCol
If table X changes, all the is needed is to ALTER the view (with no
changes) to force a recompile.
When there are two tables with identical layouts and rows are inserted
from one into the other:
INSERT X
SELECT * FROM Y
It is very hard to get that wrong. Again, if the tables change all
that is required is a recompile, removing one more chance to make an
error. If only one of the tables change the recompile will fail,
which is a Good Thing as the issue of how to deal with the change was
not addressed, and needs to be. I prefer such a failure to having the
difference remain unadressed.
Roy Harvey
Beacon Falls, CT

Listing AS stored procedures

Is there a way to list the UDFs/stored procedures in a deployed assembly?

I donno, if I really understood your question...

TO list the stroedprocedures and Functions, you can use as follows

for StoreProcedures

select name

from sys.objects

where type in (N'P', N'PC')

for Functions

select name

from sys.objects

where type in (N'FN', N'IF', N'TF', N'FS', N'FT')

|||

This works nicely in SQL Server database. How would you do the same thing in Analysis Services?

In AS you can design CLR UDFs and stored procedures and deploy them in assemblies at database or at server level. Once you have more than a handful of them deployed, you would want to be able to enumerate them without having to open the source code. How else would you make them available to everyone writing MDX against the cubes in the corresponding database?

I have looked at XMLA and schema rowsets and I do not see a way to do it.

|||There is no schema row-set way to enumerate those, but if you have the assemblies on your drive you could run ildasm utility and browse the assembly for public classes and methods. Those can be used as stored procedures.

If you do not have the copies of the assemblies on your drive, there is a way to get them from the server. I forgot how to do it but i will post another reply soon (unless somebody will answer). Of course, it will work if you have permissions to get the binary back from the server.

List user-defined objects

Hi, all. How does one list all the user-defined objects (tables, udf's,
udt's, stored procedures, and views) for a SQL Server 2000 db -- the ones
owned by dbo? Thanks.Check information schema views in BOL.
use yourDB
go
declare @.s sysname
set @.s = N'dbo'
select
table_name,
table_type
from
information_schema.tables
where
(table_type = 'base table' or table_type = 'view')
and table_schema = @.s
and objectproperty(object_id(table_schema + '.' + quotename(table_name)),
'IsMSShipped') = 0
select
*
from
information_schema.column_domain_usage
where
domain_schema = @.s
select
routine_name,
routine_type
from
information_schema.routines
where
(routine_type = 'procedure' or routine_type = 'function')
and routine_schema = @.s
go
AMB
"dw" wrote:

> Hi, all. How does one list all the user-defined objects (tables, udf's,
> udt's, stored procedures, and views) for a SQL Server 2000 db -- the ones
> owned by dbo? Thanks.
>
>|||Thank you, Alejandro. I didn't know what to look under -- now I know to
research information schema views. Thanks :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:8AC22FB8-4D41-471E-9762-1E669A1D3BBB@.microsoft.com...
> Check information schema views in BOL.
> use yourDB
> go
> declare @.s sysname
> set @.s = N'dbo'
> select
> table_name,
> table_type
> from
> information_schema.tables
> where
> (table_type = 'base table' or table_type = 'view')
> and table_schema = @.s
> and objectproperty(object_id(table_schema + '.' + quotename(table_name)),
> 'IsMSShipped') = 0
> select
> *
> from
> information_schema.column_domain_usage
> where
> domain_schema = @.s
> select
> routine_name,
> routine_type
> from
> information_schema.routines
> where
> (routine_type = 'procedure' or routine_type = 'function')
> and routine_schema = @.s
> go
>
> AMB
>
> "dw" wrote:
>|||http://www.aspfaq.com/search.asp?q=schema%3A
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:em7WicTNFHA.1884@.TK2MSFTNGP15.phx.gbl...
> Thank you, Alejandro. I didn't know what to look under -- now I know to
> research information schema views. Thanks :)
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:8AC22FB8-4D41-471E-9762-1E669A1D3BBB@.microsoft.com...
quotename(table_name)),
ones
>

List of stored procedures with permission for executing for user

I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?

Thanks for tips

If you want to get the list of stored procedures , on which specific database user ('XY') has EXECUTE permission explicitly granted , consider the following query:

Code Snippet

SELECT [name]

FROM sys.objects obj

INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id

WHERE obj.[type] = 'P' -- stored procedure

AND dp.permission_name = 'EXECUTE'

AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT

AND dp.grantee_principal_id =

(SELECT principal_id FROM sys.database_principals WHERE [name] = 'XY')

But you should be aware that here you deal only with explicitly granted permissions, not effective permissions of the user.|||

thanks a lot|||

is there any way to do this with sql server 2000?

List of stored procedures with permission for executing for user

I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?

Thanks for tips

If you want to get the list of stored procedures , on which specific database user ('XY') has EXECUTE permission explicitly granted , consider the following query:

Code Snippet

SELECT [name]

FROM sys.objects obj

INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id

WHERE obj.[type] = 'P' -- stored procedure

AND dp.permission_name = 'EXECUTE'

AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT

AND dp.grantee_principal_id =

(SELECT principal_id FROM sys.database_principals WHERE [name] = 'XY')

But you should be aware that here you deal only with explicitly granted permissions, not effective permissions of the user.|||thanks a lot|||

is there any way to do this with sql server 2000?

List of Stored Procedures

How do I get a list of the stored procedures currently in the database? Is there a quick way to edit them?

Thank you!

Hi,

if you just want to use a query you can use the INFORMATION_SCHEMA.Views to get the information. If you want to retrieve the data programmtically and as typed objects you should have a look on the SMO classes.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

select * from sys.objects where type = 'P'

Thanks
Laurentiu

List of stored procedures

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.)

List of stored procedures

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 csrPKeyYou 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).
>|||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.)|||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.)
>
>|||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.)

List of Store Procedures Called

Hi

I want to know the Store Procedures called when i run my application.We want to disable / drop the store procedures that are not called. Is there any Trace Template available to do so . We ar e using Sql Server 2000

thanks

You can run the Profiler for that:

File->New->Trace, then in Events tab choose "Stored procedure" event classes, then select only "SPtarting event" and remove others ->Run

After a period of time->Stop selected trace (red button)

List of SP names and parameters

I need to come up with a list of all Stored Procedures in
a given database and the associated parameters passed,
something like this:
Proc Name parmater
proc1 a,b
proc2 a
proc3 -
I would really appreciate if somesone can shed some light
on how to do this.
Thankswww.aspfaq.com/2463
"Adel Asaad" <Adel.Asaad@.Trade-ranger.com> wrote in message
news:52f201c34195$4880d0c0$a401280a@.phx.gbl...
> I need to come up with a list of all Stored Procedures in
> a given database and the associated parameters passed,
> something like this:
> Proc Name parmater
> proc1 a,b
> proc2 a
> proc3 -
> I would really appreciate if somesone can shed some light
> on how to do this.
> Thanks|||Thank you very much. That did it - I really appreciate
the quick response.
Adel
>--Original Message--
>www.aspfaq.com/2463
>
>
>"Adel Asaad" <Adel.Asaad@.Trade-ranger.com> wrote in
message
>news:52f201c34195$4880d0c0$a401280a@.phx.gbl...
>> I need to come up with a list of all Stored Procedures
in
>> a given database and the associated parameters passed,
>> something like this:
>> Proc Name parmater
>> proc1 a,b
>> proc2 a
>> proc3 -
>> I would really appreciate if somesone can shed some
light
>> on how to do this.
>> Thanks
>
>.
>

List of running stored procedures

Is there any way of generating a list of running stored procedures?
Thanks, Jim
Hi Jim
I assume you mean currently being run, or do you mean are accessed by an
application? You could get a list by running profiler for a (long) time but
this may not be a complete one.
John
"Jim" wrote:

> Is there any way of generating a list of running stored procedures?
> --
> Thanks, Jim
|||John,
We have a lot of procedures that run in batch and some tend to run over 8
hours. I am trying to get a list of what is still running so we can monotor
it.
Thanks, Jim
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> I assume you mean currently being run, or do you mean are accessed by an
> application? You could get a list by running profiler for a (long) time but
> this may not be a complete one.
> John
> "Jim" wrote:
|||Hi
Profiler would show you the SQL being sent to your server and DBCC
INPUTBUFFER may give you the last command for a given SPID.
John
"Jim" wrote:
[vbcol=seagreen]
> John,
> We have a lot of procedures that run in batch and some tend to run over 8
> hours. I am trying to get a list of what is still running so we can monotor
> it.
> --
> Thanks, Jim
>
> "John Bell" wrote:
|||thanks, I'll check it out.
Thanks, Jim
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Profiler would show you the SQL being sent to your server and DBCC
> INPUTBUFFER may give you the last command for a given SPID.
> John
> "Jim" wrote:
|||Jim wrote:
> John,
> We have a lot of procedures that run in batch and some tend to run over 8
> hours. I am trying to get a list of what is still running so we can monotor
> it.
Can you get what you need by querying the sysprocesses table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy
sysprocesses only returns nchar(16) for the cmd, DBCC INPUTBUFFER returns
nvarchar(255) for eventinfo.
John
"Tracy McKibben" wrote:

> Jim wrote:
> Can you get what you need by querying the sysprocesses table?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||John Bell wrote:
> Hi Tracy
> sysprocesses only returns nchar(16) for the cmd, DBCC INPUTBUFFER returns
> nvarchar(255) for eventinfo.
>
Yes, I know, I was asking the OP if he could get his SPIDs from
sysprocesses as opposed to Profiler...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

List of running stored procedures

Is there any way of generating a list of running stored procedures?
--
Thanks, JimHi Jim
I assume you mean currently being run, or do you mean are accessed by an
application? You could get a list by running profiler for a (long) time but
this may not be a complete one.
John
"Jim" wrote:

> Is there any way of generating a list of running stored procedures?
> --
> Thanks, Jim|||John,
We have a lot of procedures that run in batch and some tend to run over 8
hours. I am trying to get a list of what is still running so we can monotor
it.
--
Thanks, Jim
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> I assume you mean currently being run, or do you mean are accessed by an
> application? You could get a list by running profiler for a (long) time bu
t
> this may not be a complete one.
> John
> "Jim" wrote:
>|||Hi
Profiler would show you the SQL being sent to your server and DBCC
INPUTBUFFER may give you the last command for a given SPID.
John
"Jim" wrote:
[vbcol=seagreen]
> John,
> We have a lot of procedures that run in batch and some tend to run over 8
> hours. I am trying to get a list of what is still running so we can monoto
r
> it.
> --
> Thanks, Jim
>
> "John Bell" wrote:
>|||thanks, I'll check it out.
--
Thanks, Jim
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Profiler would show you the SQL being sent to your server and DBCC
> INPUTBUFFER may give you the last command for a given SPID.
> John
> "Jim" wrote:
>|||Jim wrote:
> John,
> We have a lot of procedures that run in batch and some tend to run over 8
> hours. I am trying to get a list of what is still running so we can monoto
r
> it.
Can you get what you need by querying the sysprocesses table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy
sysprocesses only returns nchar(16) for the cmd, DBCC INPUTBUFFER returns
nvarchar(255) for eventinfo.
John
"Tracy McKibben" wrote:

> Jim wrote:
> Can you get what you need by querying the sysprocesses table?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||John Bell wrote:
> Hi Tracy
> sysprocesses only returns nchar(16) for the cmd, DBCC INPUTBUFFER returns
> nvarchar(255) for eventinfo.
>
Yes, I know, I was asking the OP if he could get his SPIDs from
sysprocesses as opposed to Profiler...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

List of running stored procedures

Is there any way of generating a list of running stored procedures?
--
Thanks, JimHi Jim
I assume you mean currently being run, or do you mean are accessed by an
application? You could get a list by running profiler for a (long) time but
this may not be a complete one.
John
"Jim" wrote:
> Is there any way of generating a list of running stored procedures?
> --
> Thanks, Jim|||John,
We have a lot of procedures that run in batch and some tend to run over 8
hours. I am trying to get a list of what is still running so we can monotor
it.
--
Thanks, Jim
"John Bell" wrote:
> Hi Jim
> I assume you mean currently being run, or do you mean are accessed by an
> application? You could get a list by running profiler for a (long) time but
> this may not be a complete one.
> John
> "Jim" wrote:
> > Is there any way of generating a list of running stored procedures?
> > --
> > Thanks, Jim|||Hi
Profiler would show you the SQL being sent to your server and DBCC
INPUTBUFFER may give you the last command for a given SPID.
John
"Jim" wrote:
> John,
> We have a lot of procedures that run in batch and some tend to run over 8
> hours. I am trying to get a list of what is still running so we can monotor
> it.
> --
> Thanks, Jim
>
> "John Bell" wrote:
> > Hi Jim
> >
> > I assume you mean currently being run, or do you mean are accessed by an
> > application? You could get a list by running profiler for a (long) time but
> > this may not be a complete one.
> >
> > John
> >
> > "Jim" wrote:
> >
> > > Is there any way of generating a list of running stored procedures?
> > > --
> > > Thanks, Jim|||thanks, I'll check it out.
--
Thanks, Jim
"John Bell" wrote:
> Hi
> Profiler would show you the SQL being sent to your server and DBCC
> INPUTBUFFER may give you the last command for a given SPID.
> John
> "Jim" wrote:
> > John,
> >
> > We have a lot of procedures that run in batch and some tend to run over 8
> > hours. I am trying to get a list of what is still running so we can monotor
> > it.
> > --
> > Thanks, Jim
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Jim
> > >
> > > I assume you mean currently being run, or do you mean are accessed by an
> > > application? You could get a list by running profiler for a (long) time but
> > > this may not be a complete one.
> > >
> > > John
> > >
> > > "Jim" wrote:
> > >
> > > > Is there any way of generating a list of running stored procedures?
> > > > --
> > > > Thanks, Jim|||Jim wrote:
> John,
> We have a lot of procedures that run in batch and some tend to run over 8
> hours. I am trying to get a list of what is still running so we can monotor
> it.
Can you get what you need by querying the sysprocesses table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy
sysprocesses only returns nchar(16) for the cmd, DBCC INPUTBUFFER returns
nvarchar(255) for eventinfo.
John
"Tracy McKibben" wrote:
> Jim wrote:
> > John,
> >
> > We have a lot of procedures that run in batch and some tend to run over 8
> > hours. I am trying to get a list of what is still running so we can monotor
> > it.
> Can you get what you need by querying the sysprocesses table?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||John Bell wrote:
> Hi Tracy
> sysprocesses only returns nchar(16) for the cmd, DBCC INPUTBUFFER returns
> nvarchar(255) for eventinfo.
>
Yes, I know, I was asking the OP if he could get his SPIDs from
sysprocesses as opposed to Profiler...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

List of Procedures used in RDL on Production site

Is there a way to search all of the RDL's that have been deployed out to a
Reporting Services site? I am looking for what stored procedures are being
used on our production reporting site.
Any help would be appreciated.All the deployed reports are stored in a table called "catalog" you can get a
list by querying the table.
Amarnath
"LostinSpace" wrote:
> Is there a way to search all of the RDL's that have been deployed out to a
> Reporting Services site? I am looking for what stored procedures are being
> used on our production reporting site.
> Any help would be appreciated.|||I appreciate the response, but this is not what I am looking for.
I need to search the content inside every single rdl to see what stored
procedures are being used. If there isn't a way to do this in RS, is there a
way to do this from vss?
"Amarnath" wrote:
> All the deployed reports are stored in a table called "catalog" you can get a
> list by querying the table.
> Amarnath
> "LostinSpace" wrote:
> > Is there a way to search all of the RDL's that have been deployed out to a
> > Reporting Services site? I am looking for what stored procedures are being
> > used on our production reporting site.
> > Any help would be appreciated.|||OK You said "deployed" so I gave that answer...
You can search for "<CommandType>StoredProcedure</CommandType>" and the next
line will be the stored procedure name. and select look in "Current project"
from the find dialog box.
Hey this looks very basic, is this what you want ?
Amarnath
"LostinSpace" wrote:
> I appreciate the response, but this is not what I am looking for.
> I need to search the content inside every single rdl to see what stored
> procedures are being used. If there isn't a way to do this in RS, is there a
> way to do this from vss?
> "Amarnath" wrote:
> > All the deployed reports are stored in a table called "catalog" you can get a
> > list by querying the table.
> >
> > Amarnath
> >
> > "LostinSpace" wrote:
> >
> > > Is there a way to search all of the RDL's that have been deployed out to a
> > > Reporting Services site? I am looking for what stored procedures are being
> > > used on our production reporting site.
> > > Any help would be appreciated.|||Deployed is correct. I need to search every single rdl deployed to our RS site.
I need to find what stored procedures are used in each rdl. I do not seem to
be able to
do this from the ReportServer database. I know how to do this from the rdl
when in working with the rdl on its own in Visual Studio, but I have over 500
reports in RS. I need a quick and painless way to search all of the rdlâ's at
the same time to see their stored procedures.
I do appreciate the response, I just have a feeling this can not be done...
"Amarnath" wrote:
> OK You said "deployed" so I gave that answer...
> You can search for "<CommandType>StoredProcedure</CommandType>" and the next
> line will be the stored procedure name. and select look in "Current project"
> from the find dialog box.
> Hey this looks very basic, is this what you want ?
> Amarnath
>
> "LostinSpace" wrote:
> > I appreciate the response, but this is not what I am looking for.
> > I need to search the content inside every single rdl to see what stored
> > procedures are being used. If there isn't a way to do this in RS, is there a
> > way to do this from vss?
> >
> > "Amarnath" wrote:
> >
> > > All the deployed reports are stored in a table called "catalog" you can get a
> > > list by querying the table.
> > >
> > > Amarnath
> > >
> > > "LostinSpace" wrote:
> > >
> > > > Is there a way to search all of the RDL's that have been deployed out to a
> > > > Reporting Services site? I am looking for what stored procedures are being
> > > > used on our production reporting site.
> > > > Any help would be appreciated.

List all Stored Procedures used in a VS.NET project

Hi

This is kind of a visual studio question, but pertinent to db' s - due to many changes of our application I know that there are many stored procs in the database that are no longer used. Since I cannot get a list from the programmers, is there any way to get a list out of visual studio, of every SQL stored procedure called in that project? (Going through each form manually will just be too time consuming for me or the programmers)

Anyone know any tricks??
thx
Desthis query can list all the stored procedure names that ur db has...

select name from YOURDBName..sysobjects where xtype='P'|||Sorry, you misundertand - I need to list the procs used in a Visual Studio project, not in a DB..(as I know a lot are not used anymore and want to clean out my db)