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

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.
jboydellAsked:
Who is Participating?
 
jboydellConnect With a Mentor Author Commented:
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.
0
 
dbbishopConnect With a Mentor Commented:
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.

0
 
raj_Commented:
--use something like this to identify all locations
CREATE PROC dbo.sp_search_code
(
@SearchStr       varchar(100),
@RowsReturned      int = NULL      OUT
)
AS
/*************************************************************************************************
            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
            http://vyaskn.tripod.com
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
Examples:
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
*************************************************************************************************/
BEGIN
      SET NOCOUNT ON
      SELECT      DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
            CASE
                   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
END


GO
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
LowfatspreadCommented:
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....
0
 
jboydellAuthor Commented:
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.
0
 
jboydellAuthor Commented:
N/A
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.