database and create a file containing all of the SP code?
lq> 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|||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
laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404161302.54219181@.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|||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
Saghir_taj@.hotmail.com (Saghir Taj) wrote in message news:<f575b6ed.0404200602.74253ebc@.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
>
> laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404161302.54219181@.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|||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
laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404161302.54219181@.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|||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
laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404210614.674424ee@.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
> Saghir_taj@.hotmail.com (Saghir Taj) wrote in message news:<f575b6ed.0404200602.74253ebc@.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
> > laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404161302.54219181@.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|||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
laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404210614.674424ee@.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
> Saghir_taj@.hotmail.com (Saghir Taj) wrote in message news:<f575b6ed.0404200602.74253ebc@.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
> > laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404161302.54219181@.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|||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
Saghir_taj@.hotmail.com (Saghir Taj) wrote in message news:<f575b6ed.0404200602.74253ebc@.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
>
> laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404161302.54219181@.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|||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
laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404210614.674424ee@.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
> Saghir_taj@.hotmail.com (Saghir Taj) wrote in message news:<f575b6ed.0404200602.74253ebc@.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
> > laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404161302.54219181@.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|||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
laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404210614.674424ee@.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
> Saghir_taj@.hotmail.com (Saghir Taj) wrote in message news:<f575b6ed.0404200602.74253ebc@.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
> > laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404161302.54219181@.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
No comments:
Post a Comment