January 8, 2014 at 7:57 am
I have more than 100 views in a database and i would like to change the from clause for all of them in one attempt, is there a way to do that in a store proc as i may have to do that very often.
January 8, 2014 at 8:11 am
Tara-1044200 (1/8/2014)
I have more than 100 views in a database and i would like to change the from clause for all of them in one attempt, is there a way to do that in a store proc as i may have to do that very often.
To do that sort of thing in a stored proc you would have to use dynamic sql. I think it would take you longer to develop and test this solution than it would to just do it manually.
If there is a consistent pattern you could do this in SSMS pretty easily. Just add the drop and create scripts to a single file and then find and replace.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2014 at 12:53 pm
can you please give me an example how to do that?
January 8, 2014 at 1:08 pm
Tara-1044200 (1/8/2014)
can you please give me an example how to do that?
To do which one? The SSMS idea is just to use find/replace in the application.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2014 at 3:33 pm
You can display all your views in SSMS "Object Explorer Detail" window, then highlight all the views you want to change. Right click and select "Script View As" -> "Drop & Create" to "New Query Window".
You now have them all in one place and can do "Find - Replace" on the code. Then run the script to drop all the views and recreate with the new code.
In my version of SQL 2008 R0, I cannot chose ALTER as an option when selecting multiple views to script.
January 8, 2014 at 4:17 pm
Tara-1044200 (1/8/2014)
I have more than 100 views in a database and i would like to change the from clause for all of them in one attempt, is there a way to do that in a store proc as [highlight="#ffff11"]i may have to do that very often.[/highlight]
What are you changing exactly and why will you need to do it "very often"?
Answering this may help you to find a better solution.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply