Hi,
i'm sitting in front of an obscure database containing round about 400
views. :-)
I would like to export these views into an working SQL-Skript to be able
to recreate these views on a second database with an identical
table-structure.
The problem: The views are dumped to the file in the wrong order. Do you
know
this problem? What can I do?
Thanks for any hints...
Jan
Hi,
Are you going to replicate the Table and View into the new database or just
the views.
1.
If you are going to create only the views in the new database then you have
to change the script for view creation. Because your real table will
be in the old database.
it should be some thing like
Create view V1_customer as select * from olddbname..table_name
2.
Incase if you are moving both Table and the view, then create 2 scripts. 1
for tables and the other for views.
Execute the Tables first after then run the script contains the view.
Did I answered your question completely?
Thanks
Hari
MCDBA
"Jan Schmitz" <schmitz@.face.de> wrote in message
news:Oz4KJGLGEHA.712@.tk2msftngp13.phx.gbl...
> Hi,
> i'm sitting in front of an obscure database containing round about 400
> views. :-)
> I would like to export these views into an working SQL-Skript to be able
> to recreate these views on a second database with an identical
> table-structure.
> The problem: The views are dumped to the file in the wrong order. Do you
> know
> this problem? What can I do?
> Thanks for any hints...
> Jan
>
|||check out www.dbghost.com
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:%23W$dU5LGEHA.3940@.tk2msftngp13.phx.gbl...
> Hi,
> Incase if you are moving both Table and the view, then create 2 scripts. 1
> for tables and the other for views.
> Execute the Tables first after then run the script contains the view.
>
Hi,
this is what i tried. The problem ist, that the 400 Views in the
Views-Skript are dumped in the wrong order. View "B", selecting from View
"A" is created before View "A" ist created. So the generated skript simply
doesn't work. I think, what I need is a way to renew the sysdepends...
Greetings...Jan
|||"mark baekdal" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BED6BA68-89A6-42AC-A075-FF67F35D86A5@.microsoft.com...
> check out www.dbghost.com
Hi,
at first look, this sounds great - I will take a closer look at that tool...
Thanks a lot,
Jan
sql
Showing posts with label containing. Show all posts
Showing posts with label containing. Show all posts
Friday, March 23, 2012
Wednesday, March 7, 2012
Generate SPs as text
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> 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
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
Generate SPs as text
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> 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
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
Friday, February 24, 2012
Generate ALTER scripts
Hi. In SSMS (9.00.3042) I'm trying to generate a single file containing
ALTER statements for all the SPs, views and UDFs in a database. In the
Generate Scripts wizard, I've only been able to generate create and drop
statements. Any tips are appreciated.
Thanks,
BenI found a suggestion on Connect for this:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
Unfortunately, Microsoft closed the suggestion last month without fixing it.
Their comments was ... "Alter scripting post multi select isnt supported and
object alter scripts in the UI isnt going to be easy to manage." I'm not
sure what would be so difficult.
Ben|||Ben
What if you script out all objects and just REPLACE 'create' with 'alter'
( i.e in notepad or something)?
"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
> Ben|||"Uri Dimant" <urid@.iscar.co.il> wrote: in message
news:ONtFmVPjIHA.748@.TK2MSFTNGP04.phx.gbl...
> Ben
> What if you script out all objects and just REPLACE 'create' with 'alter'
> ( i.e in notepad or something)?
Hi Uri. I looked at doing that once, but noticed the word "create" was also
in the name of some SPs (e.g. CreateRecord) and in some comments within some
of these objects. I also looked at replacing "CREATE PROC" with "ALTER
PROC", but then I saw how a good number of the procedures have multiple
spaces between CREATE and PROC !! This might be possible to do, however, if
I came up with a regular expression and did a search/replace in Notepad++
(or equivalent editor). Whatever I do, I just want to make sure it's not
too difficult to be able to repeat as I may need to regenerate the ALTER
statements again.
Thank you,
Ben|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> This might be possible to do, however, if I came up with a regular
> expression and did a search/replace in Notepad++ (or equivalent editor).
These regular expressions work in Notepad++:
^\s*(create)\s+(proc)
^\s*(create)\s+(view)
^\s*(create)\s+(function)
Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
between the CREATE word and the following word (proc, view or function).
The only thing it doesn't take into account would be if CREATE and PROC were
on two different lines. But, I don't think I have any of those.|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
There is hope yet (but not for SQL Server 2008). Here is a suggestion I
submitted almost a year ago, and it is still active:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273938|||You can always write a little script in any language that supports regular
expressions to change CRAETE PROC (or a combo of all the allowed syntactic
formats) to ALTER PROC. This is a much better approach than demanding a GUI
for every little possible syntactic format.
Linchi
"Ben Amada" wrote:
> "Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> > This might be possible to do, however, if I came up with a regular
> > expression and did a search/replace in Notepad++ (or equivalent editor).
> These regular expressions work in Notepad++:
> ^\s*(create)\s+(proc)
> ^\s*(create)\s+(view)
> ^\s*(create)\s+(function)
> Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
> between the CREATE word and the following word (proc, view or function).
> The only thing it doesn't take into account would be if CREATE and PROC were
> on two different lines. But, I don't think I have any of those.
>|||"Linchi Shea" wrote:
> You can always write a little script in any language that supports regular
> expressions to change CRAETE PROC (or a combo of all the allowed syntactic
> formats) to ALTER PROC. This is a much better approach than demanding a
> GUI
> for every little possible syntactic format.
Hi. In the long run, you're probably right ... a single script or program
to do all the conversions in one shot would be best. The regular
expressions I posted a little earlier should account for all the variations
I have to deal with. I just have to do a search & replace 3 times (once for
each object type). Heck, I could have put together a small .NET app to
replace Create with Alter in less time than it took me to come up with the
regular expression :-)
ALTER statements for all the SPs, views and UDFs in a database. In the
Generate Scripts wizard, I've only been able to generate create and drop
statements. Any tips are appreciated.
Thanks,
BenI found a suggestion on Connect for this:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
Unfortunately, Microsoft closed the suggestion last month without fixing it.
Their comments was ... "Alter scripting post multi select isnt supported and
object alter scripts in the UI isnt going to be easy to manage." I'm not
sure what would be so difficult.
Ben|||Ben
What if you script out all objects and just REPLACE 'create' with 'alter'
( i.e in notepad or something)?
"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
> Ben|||"Uri Dimant" <urid@.iscar.co.il> wrote: in message
news:ONtFmVPjIHA.748@.TK2MSFTNGP04.phx.gbl...
> Ben
> What if you script out all objects and just REPLACE 'create' with 'alter'
> ( i.e in notepad or something)?
Hi Uri. I looked at doing that once, but noticed the word "create" was also
in the name of some SPs (e.g. CreateRecord) and in some comments within some
of these objects. I also looked at replacing "CREATE PROC" with "ALTER
PROC", but then I saw how a good number of the procedures have multiple
spaces between CREATE and PROC !! This might be possible to do, however, if
I came up with a regular expression and did a search/replace in Notepad++
(or equivalent editor). Whatever I do, I just want to make sure it's not
too difficult to be able to repeat as I may need to regenerate the ALTER
statements again.
Thank you,
Ben|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> This might be possible to do, however, if I came up with a regular
> expression and did a search/replace in Notepad++ (or equivalent editor).
These regular expressions work in Notepad++:
^\s*(create)\s+(proc)
^\s*(create)\s+(view)
^\s*(create)\s+(function)
Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
between the CREATE word and the following word (proc, view or function).
The only thing it doesn't take into account would be if CREATE and PROC were
on two different lines. But, I don't think I have any of those.|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
There is hope yet (but not for SQL Server 2008). Here is a suggestion I
submitted almost a year ago, and it is still active:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273938|||You can always write a little script in any language that supports regular
expressions to change CRAETE PROC (or a combo of all the allowed syntactic
formats) to ALTER PROC. This is a much better approach than demanding a GUI
for every little possible syntactic format.
Linchi
"Ben Amada" wrote:
> "Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> > This might be possible to do, however, if I came up with a regular
> > expression and did a search/replace in Notepad++ (or equivalent editor).
> These regular expressions work in Notepad++:
> ^\s*(create)\s+(proc)
> ^\s*(create)\s+(view)
> ^\s*(create)\s+(function)
> Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
> between the CREATE word and the following word (proc, view or function).
> The only thing it doesn't take into account would be if CREATE and PROC were
> on two different lines. But, I don't think I have any of those.
>|||"Linchi Shea" wrote:
> You can always write a little script in any language that supports regular
> expressions to change CRAETE PROC (or a combo of all the allowed syntactic
> formats) to ALTER PROC. This is a much better approach than demanding a
> GUI
> for every little possible syntactic format.
Hi. In the long run, you're probably right ... a single script or program
to do all the conversions in one shot would be best. The regular
expressions I posted a little earlier should account for all the variations
I have to deal with. I just have to do a search & replace 3 times (once for
each object type). Heck, I could have put together a small .NET app to
replace Create with Alter in less time than it took me to come up with the
regular expression :-)
Subscribe to:
Posts (Atom)