Replacing Table Names In Over 100 Procs

Question

Replacing Table Names In Over 100 Procs

joshdbguy

SSCertifiable

Points: 7707
More actions
January 14, 2015 at 2:47 pm

#293795

I've been tasked with migrating half of a database to a new instance. Unfortunately for me, there are over 100 stored procedures that need to be modified. Also unfortunately for me, there was no standardization in how tables were referenced. So you'll see a few different types of references.
database.dbo.table
dbo.table
table
Now, each reference needs to be server.database.schema.table.
I'm trying to come up with a good way to do this. Unfortunately since tables can be referenced differently in each stored procedure, I can't think of a great way to do this.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

JoshDBGuy (1/14/2015)
I've been tasked with migrating half of a database to a new instance. Unfortunately for me, there are over 100 stored procedures that need to be modified. Also unfortunately for me, there was no standardization in how tables were referenced. So you'll see a few different types of references.
database.dbo.table
dbo.table
table
Now, each reference needs to be server.database.schema.table.
I'm trying to come up with a good way to do this. Unfortunately since tables can be referenced differently in each stored procedure, I can't think of a great way to do this.

Instead of renaming all of the tables in the procs, consider creating synonyms named the same as the old table name and pointing to the new. Of course, pray there aren't any collisions built in.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

joshdbguy SSCertifiable Points: 7707 More actions · Answer 2

Sounds a bit funky but I guess it could work. I'd almost rather manually change them though.

JeremyE SSCoach Points: 16212 More actions · Answer 3

One way would be to:

right click the database>Tasks>Generate Scripts.

Select the 100 stored procs you need to modify and save it to a file.

Then use a text editor to do a find and replace for tablename, then schema.tablename, etc.

joshdbguy SSCertifiable Points: 7707 More actions · Answer 4

JeremyE (1/14/2015)
One way would be to:
right click the database>Tasks>Generate Scripts.
Select the 100 stored procs you need to modify and save it to a file.
Then use a text editor to do a find and replace for tablename, then schema.tablename, etc.

Unfortunately that won't be much better than doing it manually line by line. It also has some inherent issues in a few situations that make that solution problematic.

It sounds like a may be going this manually.

Sigh.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

Not something I've tried, but you could look at Red Gate SQL Dependency Tracker[/url]. It does a pretty good job of finding everything that references a table (or other objects). It'll at least give you a good list to work from. Prompt has a Refactor command that does object renames and tracks down dependencies. It might also be worth a shot. Again, not something I've tried with such an extensive and variable set of requirements.

Another option, look to see if you can use regular expressions and a powershell script to identify and modify the objects. On this, we're officially out of my league. But trying to come up with something other than search & replace, which won't work real well.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

joshdbguy SSCertifiable Points: 7707 More actions · Answer 6

So, here's what I'm going to do. Every table has a space or new line before each record. So I'll generate a list of tables and then run a replace using that list in each proc, looking for char(13) + table string or char(10) + table string. I'm going to run a test first as I'm not sure how slow it's going to be.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 7

Why not just use the tools that MS give you with the SSDT, reverse engineer the database into an SQL DB Project in VS2012 shell (or Vs2010 shell), then use the built in REFACTOR option, which will show you all the changes that are required.

you can also add the DB project to TFS to add a degree of change management control to the whole process.

The best bit is its FREE with SQL Server.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Another free option (I have no affiliation) is Apex SQL Refactor.

http://www.apexsql.com/sql_tools_refactor.aspx

Edit: Actually, now I'm not sure if that would do the full 4PN refactoring..but maybe worth a quick look.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);