November 12, 2007 at 10:57 am
Hy everybody,
I have to change a table name in tons of views. Would like not do it manually 😉
Any suggestion?
Thank you
Ruggiero
November 12, 2007 at 12:34 pm
I don't think what I am about to suggest is the best way to go, but it satisfies your non-manual constraint:
1. Script out all views, together with exec privileges as a single text file
2. Find/Replace all necessary table names in the script text file
3. Drop all views in question
4. Recreate all views using the script of step 2
As mentioned earlier - not best way, and might not work in case of some dependencies ... but 'a way'
Good luck!
November 12, 2007 at 12:41 pm
sql_er (11/12/2007)
I don't think what I am about to suggest is the best way to go, but it satisfies your non-manual constraint:1. Script out all views, together with exec privileges as a single text file
2. Find/Replace all necessary table names in the script text file
3. Drop all views in question
4. Recreate all views using the script of step 2
As mentioned earlier - not best way, and might not work in case of some dependencies ... but 'a way'
Good luck!
I think this is probably your best bet. Just be very careful as you do your replaces not to replace what you don't intend. For example if you are changing Custom to Unique you don't want to change Customer to Uniqueer or something silly like that. Management Studio can fairly easily script out all of the views into 1 script. The only thing I would change from the above is step 3. I would use alters instead of drops. That way you avoid any premissions & dependencies problems.
Also make sure you parse the script first to make sure you havn't made any mistakes. It won't catch all of them by any means but it may catch a few.
Kenneth
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 12, 2007 at 1:07 pm
Cannot afford this task using sql script generated because of dependency, I've got about 500 view and sql doesn't manage nested views dependency.
I'm trying Apex sql script, but there are some problems also there.
Any other idea?
Thks
November 12, 2007 at 1:14 pm
Actually as long as you use an ALTER instead of a DROP and CREATE you should avoid the dependency problem.
You might be able to write a SQL DMO script to run through the view objects and script each one out .. then do a replace on the output, then run the output. Or something like that .. but like I said before you run a serious risk of changing things you don't intend. Unless you can be absolutly certain that you arn't going to change cases you don't intend I wouldn't follow this route.
For example if you decide to expand your abbreviation of Certification (Cert to Certification) and ran it on the above text you would end up changing the word certain to Certificationain.
For things like this sometimes the grunt work way is the best way.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 13, 2007 at 2:39 am
Well Kenneth,
the alter statement instead of drop/create seems to be a good way.
Yes I understand the risk of changing something wrong, working carefully I'm sure I can avoid errors.
Now a question, me be stupid:
1) Replace table name in syscomments.text field
2) sp_refreshview on all views in the DB
Could work?
Tks again
November 13, 2007 at 7:12 am
In 2000 that might work but in 2005 you can't alter the system tables anymore.
Like I said you could go the DMO route but probably just scripting the whole thing with alters and doing replaces will be the easiest and safest.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 13, 2007 at 7:22 am
Yes I'm following the ALTER way, I agree it's the best one.
It was just a question to understand if it was a stupid idea or just a strange one 🙂
Thks again
November 13, 2007 at 7:25 am
Definatly not stupid. And had you been working in 2000 it would have been a reasonable one (minus all of the problems I've mentioned above). Fortunatly (or unfortunatly depending on how you look at it) SQL 2005 doesn't allow modifications to the system tables.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 13, 2007 at 7:31 am
Depends on guests, some of them don't want to migrate to 2005 because everything is working fine and by their point of view there no need to change, and I partially agree 😉
November 13, 2007 at 7:35 am
ops guests=customers sorry about my english I do the best i can :hehe:
November 14, 2007 at 7:13 am
Why not use a SYNONYM?
This is an easy solution as long as the "old" table doesn't remain.
You can create a SYNONYM referencing the "old" table to the "new" table. Then recompile (refresh) the views.
Sample T-SQL to prove my point:
create table old_table (foo int);
insert into old_table values (1);
select * from old_table;
go
create table new_table (foo int);
insert into new_table values (-1);
select * from new_table;
go
CREATE view my_view (foo) as select foo from old_table;
go
select * from my_view;
go
drop table old_table;
create synonym old_table for new_table;
EXECUTE sp_refreshview 'my_view';
select * from my_view;
go
November 14, 2007 at 7:49 am
Ok it seems I've to give more details about the problem I'm solving.
A customer has a table containing doctors data (name, surname, email, telephone, mobile, address...) now we understand that we need to have more than 1 address for each doctor (ie home address, hospital, etc) so I built another called doctor_addresses that has a relation many to 1 with doctors, then made a view doctor_with_address putting the doctor table in left outer join with doctor_addresses, but only with 1 address tagged as "main address" (via trigger I managed the univocity of this address in case doctor has got more than 1 address), this view is also updetable.
Now this view contains exactly the some fields of the old doctor table.
Next task that I'm affording is to change all views and stored procs that use tha doctor table, and let them point to the view doctor_with_address. In the while we are modifing programs pointing explictly to the doctor table.
Then, after populating the address table with actual data from doctor, I'll deleted the address's field from doctor table, that still remains. So the Synonim would'nt work.
However thanks for your suggestion
November 14, 2007 at 8:08 am
In that case I would change name of the Doctor table to Doctor_Name and the name of the view to Doctor. That way your code and views and SPs etc will still work fine. You might need to recompile everything but thats not exactly difficult.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 14, 2007 at 8:36 am
Well this has only a problem, our doctors table contains about 100k records, their address are supposed to be the some number (now) or more (in the next future), a join between these two tables, also if well indexed, is however an heavy job. If somewhere my need is to retrive just doctor's name or email I prefer not to use the view but only the main table. So I'll not make a replace all operation but an analitical replace in the views, if it shows spme address field I'll substitute table with the view, otherwise not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply