Changing table name used in function, stored procedure and views

Question

Changing table name used in function, stored procedure and views

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
July 30, 2018 at 6:49 am

#407262

Our ERP Vendor decided to change the tables names. For example there use to be a table called customer now its called customer_mst, they added _mst to all the tables.
is there any way to change all custome functions, stored procedure and view to use new table names.Post new topic

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Why would anyone do that?! I think it's going to be messy. You can use the sys.sql_modules catalog view to find all objects that have the word "customer" in their definition. It may throw up a few false positives - if you have the word in a comment, or if you have a table called CustomerAddress, for example. Don't forget to check synonyms, jobs, linked servers, SSIS packages and application code as well.

John

tripleAxe SSCertifiable Points: 5706 More actions · Answer 2

I would leave custom functions, stored procedures and views as they are, and instead of changing them I would create new views for each of the tables with the _mst suffix naming the view as the table name without the _mst suffix. For example

CREATE VIEW customer AS SELECT [Col1],[Col2],[Col3] FROM customer_mst

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 3

The danger there is losing the use of indexes, especially in joins, & getting a big performance hit...

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

As already pointed out the views solution could leave you with some serious performance issues. But this sounds like a great time to learn about synonyms. You can create a synonym for each base table with the old name. No loss of performance whatsoever.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

tripleAxe SSCertifiable Points: 5706 More actions · Answer 5

Yes! I agree with the previous 2 posters; Synonyms would be much better.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

If the VIEWS are created correctly, they they'll use the underlying indexes, etc, of the tables just fine.

I do agree with the others that the use of SYNONYMs are better here.

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

laurie-789651 - Monday, July 30, 2018 7:12 AM
The danger there is losing the use of indexes, especially in joins, & getting a big performance hit...

If the views are setup correctly, there is no danger here. The underlying indexes will be used just fine and like they always have been.

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

andycadley SSCertifiable Points: 5296 More actions · Answer 8

Synonyms are probably the neatest solution in the short term, but I'd bear in mind that there's (probably) a reason other than "we felt like it" that the vendor changed the tables. It might be because something fundamental has changed about them and it was easier for them to rename them all then ensure all accessing code was updated with new assumptions. Or it may be that in the next version they're planning to introduce a whole new bunch of tables with the old names but different structures. Or it may be something entirely different.

In any case, in the long term it may be better to bite the bullet and update all your custom functions to use the new names, to avoid having to pick apart a bigger problem the next time you get an update.