How to get the renamed objects list from the database?

Hi all, I have a database with fewer objects, where I have performed sp_rename operation on a few of them. Even though, I am seeing the change in object name But the definition of those objects was not changed in sql_modules. 1. Is there any flag that shows like object is renamed? 2. How to get the old object name of the changed object name?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM commented · Sep 09, 2017 at 06:42 PM

As far as I know, unless the database is in source control, there isn't a way to accomplish this. You could also use a backup (if you have one) and compare object_id's as I believe they should be the same even after a rename.

1 ·

ThomasRushton ♦♦ commented · Sep 12, 2017 at 09:02 AM

Also, consider using a tool such as Redgate's DLM Dashboard - http://www.red-gate.com/products/dlm/dlm-dashboard/ - as this will monitor your schemas and identify what has changed / what's different between Dev/Test/Production etc. It's free. Obviously, it won't help with figuring what has changed already, but it might help identify changes in the future.

1 ·

schindam26 commented · Sep 11, 2017 at 03:06 PM

yep, you're right John M. Object_ID's will be same after rename. I feel, there is no other way to solve it until we run Drop and Create object.

0 ·

JohnM commented · Sep 11, 2017 at 10:06 PM

Running a DROP/CREATE won't solve it either however. The old object will be dropped and the new object would have a new name. You could, I suppose move the old object into a different schema (like archive or something) and then create the new object in the previous schema. However, I would highly recommend some type of source control to help with this.

0 ·

schindam26 commented · Sep 13, 2017 at 08:00 AM

Thanks JohnM, It sounds good. Thanks ThomasRushton, I will use the suggested tool for my server databases, which could help me in solving my issue.

0 ·