Change table name

Question

Change table name

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
January 10, 2013 at 6:56 am

#269179

Hi All,
I know you can change table name using the sp_rename 'old_table_name', new_table_name', but I am more concerned about what effects it will have or what are the things I need to check before changing table name?
This is not in prod yet so, I would think it's best to change it now.
Can you share your experience.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Any references to that table will break once the name is changed, so things like views, functions, procedures etc will need checking to see if they reference the table in anyway.

The sys.sql_modules table will be a very good place to start by quering the definition column for the table name in question.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

anthony.green (1/10/2013)
Any references to that table will break once the name is changed, so things like views, functions, procedures etc will need checking to see if they reference the table in anyway.
The sys.sql_modules table will be a very good place to start by quering the definition column for the table name in question.

So, you are saying I have change any references to this table (like views, functions, procedures etc) manually?

SueTons.

Regards,
SQLisAwe5oMe.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

Yes that is correct, any referencing objects need to be manually updated with the new table name, SQL wont do it for you.

It even gives you a warning once the rename has happened to detail this could break things.

Changing any part of an object name can break scripts and stored procedures.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 4

Thanks Anthony....appreciate your quick response.

SueTons.

Regards,
SQLisAwe5oMe.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 5

The sys.sql_modules table will be a very good place to start by quering the definition column for the table name in question.

Anthony, What exactly am I looking here(sys.sql_modules).

SueTons.

Regards,
SQLisAwe5oMe.

Ant-Green SSC Guru Points: 113507 More actions · Answer 6

Its the metadata of objects like views, triggers, procedures.

You can query it, using a open ended like clause on the definition column for the table name in question

SELECT * FROM sys.sql_modules WHERE definition LIKE '%tablename%'

If it brings anything back you know you need to change them objects.

Its not a 100% complete list as you may have processes on other servers, systems, apps which reference the table, so you would need to do a complete invesitgation as to what connects to the DB, and loop through source code or jobs etc to find out what else will break.

Or you could just go gung-ho and change the table and wait to see what breaks.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 7

SQLisAwe5oMe

SSChampion

Points: 14052

January 10, 2013 at 7:38 am

#1575805

Great, Thanks again.

SueTons.

Regards,
SQLisAwe5oMe.

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 8

Easiest way to determine what else will need to be changed is to right-click the table in SSMS and click View Dependencies. This will tell you everything that references it with the least effort.