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

Posted on 2007-08-06
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

    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.

    LVL 8

    Expert Comment

    --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
    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
          SET NOCOUNT ON
          SELECT      DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME( AS 'Object name',
                       WHEN OBJECTPROPERTY(, 'IsReplProc') = 1
                            THEN 'Replication stored procedure'
                       WHEN OBJECTPROPERTY(, 'IsExtendedProc') = 1
                            THEN 'Extended stored procedure'                        
                      WHEN OBJECTPROPERTY(, 'IsProcedure') = 1
                            THEN 'Stored Procedure'
                      WHEN OBJECTPROPERTY(, 'IsTrigger') = 1
                            THEN 'Trigger'
                      WHEN OBJECTPROPERTY(, 'IsTableFunction') = 1
                            THEN 'Table-valued function'
                      WHEN OBJECTPROPERTY(, 'IsScalarFunction') = 1
                            THEN 'Scalar-valued function'
                       WHEN OBJECTPROPERTY(, 'IsInlineFunction') = 1
                            THEN 'Inline function'      
                       WHEN OBJECTPROPERTY(, 'IsView') = 1
                            THEN 'View'      
                       WHEN OBJECTPROPERTY(, 'IsTable') = 1
                            THEN 'Table'      
                END AS 'Object type',
                'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME( + '''' AS 'Run this command to see the object text'
          FROM      syscomments c
                INNER JOIN
                sysobjects o
                ON =
          WHERE      c.text LIKE '%' + @SearchStr + '%'      AND
                encrypted = 0                        AND
                OBJECTPROPERTY(, 'IsReplProc') = 1            OR
                OBJECTPROPERTY(, 'IsExtendedProc') = 1      OR
                OBJECTPROPERTY(, 'IsProcedure') = 1            OR
                OBJECTPROPERTY(, 'IsTrigger') = 1            OR
                OBJECTPROPERTY(, 'IsTableFunction') = 1      OR
                OBJECTPROPERTY(, 'IsScalarFunction') = 1      OR
                OBJECTPROPERTY(, 'IsInlineFunction') = 1      or
                OBJECTPROPERTY(, 'IsView') = 1      or
                OBJECTPROPERTY(, 'IsTable') = 1      
          ORDER BY      'Object type', 'Object name'
          SET @RowsReturned = @@ROWCOUNT

    LVL 50

    Expert Comment

    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....

    Author Comment

    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

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now