Effect Of Table Name Change (Using SP_RENAME) On Stored Procedures

Hi,

Recently I came across a post on MSDN its was interesting. Lets see what it was.

Q: When we rename a table using SP_RENAME command, does it affect Stored Procedure
that were depending on it?

A: I did a small repro lets discuss it here.

--Below statement will create a table TEST.
create table test (I int unique, name varchar(10),age int)
go

--Below statements will insert 3 records in table TEST.
insert into test values (1,'Robert',20)
insert into test values (2,'Bob',21)
insert into test values (3,'Sam',30)
go

--Below statement will create a procedure on table TEST (a simple SELECT * FROM TEST).
create procedure USP_SELECT_TEST AS
SELECT * FROM TEST
go

--Below statement will create a table named TEST_TMP (replica of TEST table).
CREATE TABLE TEST_TEMP (I INT UNIQUE, NAME VARCHAR(10), AGE INT)
GO

--Below statement will insert values in newly created table TEST_TEMP from TEST table.
INSERT INTO TEST_TEMP SELECT * FROM TEST
GO

--Below statements will get OBJECT ID for TEST, TEST_TEMP & USP_SELECT_TEST objects created above.
SELECT OBJECT_ID('TEST')
SELECT OBJECT_ID('TEST_TEMP')
SELECT OBJECT_ID('USP_SELECT_TEST')
GO
--Output for each above command respectively
Object Name Object ID
TEST 1723153184
TEST_TEMP 1771153355
USP_SELECT_TEST 1755153298

--Below statement will show dependencies of above created objects.

SELECT * FROM SYS.SQL_DEPENDENCIES

class class_desc object_id column_id referenced_major_id referenced_minor_id is_selected is_updated is_select_all
----- ------------------------------------------------------------ ----------- ----------- ------------------- ------------------- ----------- ---------- -------------
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 1 0 0 1
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 2 0 0 1
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 3 0 0 1

--So in above output what we are seeing is that stored procedure USP_SELECT_TEST having OBJECT ID 1755153298
--is refered by table TEST having object id 1723153184 (which is fare statement isn't it).


--Below statement will rename table TEST to TEST_OLD.
SP_RENAME 'TEST','TEST_OLD'
GO

Output
Caution: Changing any part of an object name could break scripts and stored procedures.

--Below statement will rename TEST_TEMP to TEST.
SP_RENAME 'TEST_TEMP','TEST'
GO
Output
Caution: Changing any part of an object name could break scripts and stored procedures.

--Again running OBJECT ID statement to find if after rename OBJECT_ID has changed or not.
SELECT OBJECT_ID('TEST')
SELECT OBJECT_ID('TEST_TEMP')
SELECT OBJECT_ID('USP_SELECT_TEST')
SELECT OBJECT_ID('TEST_OLD')

--Output for each above command respectively
Object Name Object ID
TEST 1771153355
TEST_TEMP NULL (As there is no table with name TEST_TEMP.)
USP_SELECT_TEST 1755153298
TEST_OLD 1723153184

Now if we compare both OBJECT_ID outputs we are seeing that previously (Prior to SP_RENAME command)
table TEST was having OBJECT_ID 1723153184 but now it has value 1771153355 (which was previously allocated
to table TEST_TEMP) no why it is like this. This is because only the logical name of the table has changed
in actual OBJECT_ID remains the same.

Now if we again run the SYS.SQL_DEPENDENCIES statement we will see.

SELECT * FROM SYS.SQL_DEPENDENCIES

class class_desc object_id column_id referenced_major_id referenced_minor_id is_selected is_updated is_select_all
----- ------------------------------------------------------------ ----------- ----------- ------------------- ------------------- ----------- ---------- -------------
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 1 0 0 1
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 2 0 0 1
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 3 0 0 1

So USP_SELECT_TEST having OBJECT_ID 1755153298 is still referring to table TEST_OLD with OBJECT_ID 1723153184 however
currently table TEST is having OBJECT_ID 1771153355.
So that means that if we use SP_RENAME statement it will only rename the NAME of the object not the underlying OBJECT_ID. OBJECT_ID will remain the same. Also in meta data tables objects are refered with OBJECT_ID's not NAME.

That is the reason when you check DEPENDENCIES in Management Studio it still shows old OBJECT i.e. TEST_OLD.


Regards
Gurpreet Sethi