Generate SPs as text

[Lauren Quantrell]

Is there an easy way to loop through every stored procedure in a
database and create a file containing all of the SP code?
lq

[Craig Kelly]

> Is there an easy way to loop through every stored

procedure in a database and create a file containing
all of the SP code?
lq

I would suggest one of three ideas (in order of ease of use and
compatibility across versions of SQL Server):

1. Enterprise Manager can do this for you

2. You can write a program using SQLDMO to do this. If you've never used
SQLDMO before, you'll want to loop through the StoredProcedure objects in
the Database object. You'll also want to look up the SystemObject property
and the Script method. In addition, we've found that it's easier to have
SQLDMO script the actual proc, while we handle adding script for dropping
the procedure before creating and for granting permissions (but this will
depend on how you control SP's and their permissions).

3. See the technique I mentioned in the thread "Search contents of stored
procedures?" for a down-and-dirty (not to mention fragile) technique.

Craig

[Saghir Taj]

Dear Laurenquantrell

By joining sysobject and syscomments table we can get your desired results,
run this query in Query Analyzer (with result in text mode)....
-----------------------------------------------------------------------------
SELECT dbo.syscomments.text, dbo.sysobjects.name
FROM dbo.syscomments INNER JOIN
dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.xtype = 'p')
-----------------------------------------------------------------------------
Best of luck!

Saghir Taj

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Is there an easy way to loop through every stored procedure in a
database and create a file containing all of the SP code?
lq

[Lauren Quantrell]

Thank you very much for that. It is very helpful. Now I just need an
easy way to unencrypt all the SPs I encrypted with "With
Encryption"...
lq

Sa********@hotmail.com (Saghir Taj) wrote in message news:<f5**************************@posting.google. com>...

Dear Laurenquantrell

By joining sysobject and syscomments table we can get your desired results,
run this query in Query Analyzer (with result in text mode)....
-----------------------------------------------------------------------------
SELECT dbo.syscomments.text, dbo.sysobjects.name
FROM dbo.syscomments INNER JOIN
dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.xtype = 'p')
-----------------------------------------------------------------------------
Best of luck!

Saghir Taj

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Is there an easy way to loop through every stored procedure in a
database and create a file containing all of the SP code?
lq

[Saghir Taj]

Dear Laurenquantrell

By joining sysobject and syscomments table we can get your desired results,
run this query in Query Analyzer (with result in text mode)....
-----------------------------------------------------------------------------
SELECT dbo.syscomments.text, dbo.sysobjects.name
FROM dbo.syscomments INNER JOIN
dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.xtype = 'p')
-----------------------------------------------------------------------------
Best of luck!

Saghir Taj

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Is there an easy way to loop through every stored procedure in a
database and create a file containing all of the SP code?
lq

[Saghir Taj]

Dear

I am afraid that we can not decrypt a encrypted object! till the SQL
2000. so wait till i find any undocmented function or third party tool
which can do the trick...

Best of luck

Me,
Saghir Taj



la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Thank you very much for that. It is very helpful. Now I just need an
easy way to unencrypt all the SPs I encrypted with "With
Encryption"...
lq

Sa********@hotmail.com (Saghir Taj) wrote in message news:<f5**************************@posting.google. com>...

Dear Laurenquantrell

By joining sysobject and syscomments table we can get your desired results,
run this query in Query Analyzer (with result in text mode)....
-----------------------------------------------------------------------------
SELECT dbo.syscomments.text, dbo.sysobjects.name
FROM dbo.syscomments INNER JOIN
dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.xtype = 'p')
-----------------------------------------------------------------------------
Best of luck!

Saghir Taj

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Is there an easy way to loop through every stored procedure in a
database and create a file containing all of the SP code?
lq

[Saghir Taj]

Dear

I am afraid that we can not decrypt a encrypted object! till the SQL
2000. so wait till i find any undocmented function or third party tool
which can do the trick...

Best of luck

Me,
Saghir Taj



la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Thank you very much for that. It is very helpful. Now I just need an
easy way to unencrypt all the SPs I encrypted with "With
Encryption"...
lq

Sa********@hotmail.com (Saghir Taj) wrote in message news:<f5**************************@posting.google. com>...

Dear Laurenquantrell

By joining sysobject and syscomments table we can get your desired results,
run this query in Query Analyzer (with result in text mode)....
-----------------------------------------------------------------------------
SELECT dbo.syscomments.text, dbo.sysobjects.name
FROM dbo.syscomments INNER JOIN
dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.xtype = 'p')
-----------------------------------------------------------------------------
Best of luck!

Saghir Taj

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Is there an easy way to loop through every stored procedure in a
database and create a file containing all of the SP code?
lq

[Lauren Quantrell]

Thank you very much for that. It is very helpful. Now I just need an
easy way to unencrypt all the SPs I encrypted with "With
Encryption"...
lq

Sa********@hotmail.com (Saghir Taj) wrote in message news:<f5**************************@posting.google. com>...

Dear Laurenquantrell

By joining sysobject and syscomments table we can get your desired results,
run this query in Query Analyzer (with result in text mode)....
-----------------------------------------------------------------------------
SELECT dbo.syscomments.text, dbo.sysobjects.name
FROM dbo.syscomments INNER JOIN
dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.xtype = 'p')
-----------------------------------------------------------------------------
Best of luck!

Saghir Taj

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Is there an easy way to loop through every stored procedure in a
database and create a file containing all of the SP code?
lq

[Saghir Taj]

Dear

I am afraid that we can not decrypt a encrypted object! till the SQL
2000. so wait till i find any undocmented function or third party tool
which can do the trick...

Best of luck

Me,
Saghir Taj



la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Thank you very much for that. It is very helpful. Now I just need an
easy way to unencrypt all the SPs I encrypted with "With
Encryption"...
lq

Sa********@hotmail.com (Saghir Taj) wrote in message news:<f5**************************@posting.google. com>...

Dear Laurenquantrell

By joining sysobject and syscomments table we can get your desired results,
run this query in Query Analyzer (with result in text mode)....
-----------------------------------------------------------------------------
SELECT dbo.syscomments.text, dbo.sysobjects.name
FROM dbo.syscomments INNER JOIN
dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.xtype = 'p')
-----------------------------------------------------------------------------
Best of luck!

Saghir Taj

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Is there an easy way to loop through every stored procedure in a
database and create a file containing all of the SP code?
lq

[Saghir Taj]

Dear

I am afraid that we can not decrypt a encrypted object! till the SQL
2000. so wait till i find any undocmented function or third party tool
which can do the trick...

Best of luck

Me,
Saghir Taj



la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Thank you very much for that. It is very helpful. Now I just need an
easy way to unencrypt all the SPs I encrypted with "With
Encryption"...
lq

Sa********@hotmail.com (Saghir Taj) wrote in message news:<f5**************************@posting.google. com>...

Dear Laurenquantrell

By joining sysobject and syscomments table we can get your desired results,
run this query in Query Analyzer (with result in text mode)....
-----------------------------------------------------------------------------
SELECT dbo.syscomments.text, dbo.sysobjects.name
FROM dbo.syscomments INNER JOIN
dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.xtype = 'p')
-----------------------------------------------------------------------------
Best of luck!

Saghir Taj

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

Is there an easy way to loop through every stored procedure in a
database and create a file containing all of the SP code?
lq