How to Search and Replace only table names in a SQL 2000 script.

Posted on 2007-08-06
Medium Priority
Last Modified: 2012-06-21
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.
Question by:jboydell
LVL 15

Assisted Solution

dbbishop earned 200 total points
ID: 19639517
All I can think of is to search for 'FROM COMPANY' (selecting from the company table), 'JOIN COMPANY' (the company table is part of a join) 'COMPANY.' (the company table name is prefixed to a field name), 'UPDATE COMPANY', 'DELETE [ FROM] COMPANY', 'INSERT [ INTO] COMPANY'

You may have to do a second check for dbo. prefix (e.g. FROM dbo.Company, etc.).

That would get most of the standard code, including code to DROP the objects. I'd script the stored procedures into a single script file so you could do it all at once. Save a copy of the script (so you can restore to original from if something bombs out) and aply the changes to the script.


Expert Comment

ID: 19639548
--use something like this to identify all locations
CREATE PROC dbo.sp_search_code
@SearchStr       varchar(100),
@RowsReturned      int = NULL      OUT
            Copyright c 1997 - 2002 Narayana Vyas Kondreddi. All rights reserved.
Purpose:      To search the stored proceudre, UDF, trigger code for a given keyword.
Written by:      Narayana Vyas Kondreddi
Tested on:       SQL Server 7.0, SQL Server 2000
Date created:      January-22-2002 21:37 GMT
Date modified:      February-17-2002 19:31 GMT
Email:             vyaskn@hotmail.com
To search your database code for the keyword 'unauthorized':
EXEC sp_search_code 'unauthorized'
To search your database code for the keyword 'FlowerOrders' and also find out the number of hits:
DECLARE @Hits int
EXEC sp_search_code 'FlowerOrders', @Hits OUT
SELECT 'Found ' + LTRIM(STR(@Hits)) + ' object(s) containing this keyword' AS Result
      SELECT      DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
                   WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
                        THEN 'Replication stored procedure'
                   WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
                        THEN 'Extended stored procedure'                        
                  WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
                        THEN 'Stored Procedure'
                  WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
                        THEN 'Trigger'
                  WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
                        THEN 'Table-valued function'
                  WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
                        THEN 'Scalar-valued function'
                   WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
                        THEN 'Inline function'      
                   WHEN OBJECTPROPERTY(c.id, 'IsView') = 1
                        THEN 'View'      
                   WHEN OBJECTPROPERTY(c.id, 'IsTable') = 1
                        THEN 'Table'      
            END AS 'Object type',
            'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'
      FROM      syscomments c
            INNER JOIN
            sysobjects o
            ON c.id = o.id
      WHERE      c.text LIKE '%' + @SearchStr + '%'      AND
            encrypted = 0                        AND
            OBJECTPROPERTY(c.id, 'IsReplProc') = 1            OR
            OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1      OR
            OBJECTPROPERTY(c.id, 'IsProcedure') = 1            OR
            OBJECTPROPERTY(c.id, 'IsTrigger') = 1            OR
            OBJECTPROPERTY(c.id, 'IsTableFunction') = 1      OR
            OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1      OR
            OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1      or
            OBJECTPROPERTY(c.id, 'IsView') = 1      or
            OBJECTPROPERTY(c.id, 'IsTable') = 1      
      ORDER BY      'Object type', 'Object name'
      SET @RowsReturned = @@ROWCOUNT

LVL 50

Expert Comment

ID: 19639976
you need to go back and actually process the sysdepends output
and then generate files containing just the appropriate objects ...

then the scope of your actions can be limited...

be aware that sp_depends  etc can only work on static code, if you have code which is dynamic
then you have a potentially impossible task to perform....
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database


Author Comment

ID: 19640132
Thanks for your input guys... I have a script containing only the objects (stored procedures, views, functions) that needs to be updated... but I still need a way of only replacing the "Company" table reference with the view reference.  This is the bit that I am really stuck on.

Accepted Solution

jboydell earned 0 total points
ID: 19652556
I've run out time on this so I had to do it using Search and replace.  I setup a series of search and replace actions for all obvious table references such as Company INNER JOIN and finally did a manual search and replace with confirmation to make sure I have caught all the remaining ones.  It's the best I could do but I got the time for the operation down to a couple of minutes by automating the process using TextPipe Pro.

Author Comment

ID: 22374874

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question