June 26, 2009 at 6:10 am
hi friedns,
I have one situation...we have here more then 250 views directed to database db1 like all views having from db1..sometable.
now we have to change these 250 views to db2..sometable .
so i have to go to each view and change it manually...
any other way we can do by wrting any sp or code?
any suggestion will be appreciated.... thx in advance...
Mithun
June 26, 2009 at 6:23 am
You could loop through all views and replace db1 with db2 in the text returned by sp_helptext @viewName.To get an alter statement you will have to replace again CREATE with ALTER.
Could this fit your needs?
-- Gianluca Sartori
June 26, 2009 at 6:24 am
You could also create synonyms in db1 for the tables in db2.
I don't like this solution anyway, it's not very clean.
-- Gianluca Sartori
June 26, 2009 at 6:46 am
yeah this coluld be done , i can loop this view and using sp_helptext i can replace db1 with db2... seems cool let me give it a try ....
thx a lot
Mithun
June 26, 2009 at 7:19 am
OBJECT_DEFINITION works nicely for views.
You can also look in the definition column of sys.sql_modules - join from sys.views to see only the view definitions.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 29, 2009 at 7:09 am
HEY PAUL
THANKS FOR THIS ONE sys.sql_modules ...
it really helped me...
Mithun
June 29, 2009 at 2:42 pm
No worries.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 26, 2011 at 1:15 pm
post moved to general sql server 2008 forum
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply