How to Search and Replace only table names in a SQL 2000 script.
Posted on 2007-08-06
Hi guys, I am desperately trying to find a way to search and replace in a SQL script but only replace instances of a table name.
For example... I have scripted all the views, functions and stored procedures in my database and I want to replace all instances of the table "Company" with a reference to a view called "vCompany". Obviously a standard search and replace is not clever enough because many stored procedure and column names contain the word "Company" and these would also get replaced.
I have done sp_depends to find out how many objects references the Company table and there are 167 so I don't want to do this manually. Also, I am currently doing this exercise on the development database and I will have to do it again on test and live servers. I need to find a way to automate this process somehow.
I cannot rename the Company table and substitute a view with the same name because two applications access the table. One needs to see all the data in the company table and the other needs to only see the data as restricted by the vCompany view.
It seems to me that because some products parse SQL to provide syntax highlighting it might be possible to determine if a reference to "Company" is a tablename. If anyone know of a way of doing this even if it means buying a third party product then please let me know.