1

Last week, as a part of one requirement, I wrote a script to generate object definition for all database objects.

While testing, I found that for few stored procedures, [sys.objects].name was not matching with name in actual object definition (as returned by OBJECT_DEFINITION(Object_ID)). It was very strange and I saw it first time in life.

While thinking for what could be the reason of same, I found that it happens when we rename stored procedure from Object Explorer (View--> Object Explorer in SQL Server Management studio).

Just wondering, is there any way to retrieve actual object name (in this case, SP name)?

Savani Mahesh
  • 11
  • 2

2 Answers2

1

I had the same problem. It appears SP_RENAME doesn't update the object definition. From the sp_rename link, microsoft says:

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object either in the definition column of the sys.sql_modules catalog view or obtained using the OBJECT_DEFINITION built-in function. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.

user2444499
  • 757
  • 8
  • 14
0

I had a similar situation, where a stored procedure had been renamed using sp_rename. The new name used was iterative, with an extension of *_1. If the original name was 'MyProcedure', the new name would have been 'MyProcedure_1'.

'MyProcedure' represented the production code. 'MyProcedure_1' represented code that had been production code at one time, but was now obsolete. Indeed, 'MyProcedure_1' referenced objects that were no longer present in the database.

When looking at the name in sys.objects, the new name with the extension was there ('MyProcedure_1'), but the object_definition returned the old name ('MyProcedure'), but with the code for 'MyProcedure_1'.

This was discovered via a call to 'MyProcedure', which errored out, due to the missing objects.

The solution was to DROP 'MyProcedure_1', which effectively removed the object_id, and the erroneous object_definition, and reCREATE it. Of course we had to address the missing objects. And - we spread caution about using sp_rename for objects other than tables/columns.

Ash
  • 1