Using sp_rename on a table updates the foreign keys for the table when documentation seems to say it wont

Question

I must be misunderstanding the Microsoft documentation because it says

Renaming a table will not automatically rename references to that table. You must manually modify any objects that reference the renamed table.

However, when I ran the sp_rename on my table in addition to renaming the table it also changed the foreign keys connected to the table. I'm not complaining, because they needed to be changed, I'm just confused why it said one thing and then did another. How is a foreign key not an object? The sp_rename documentation seems to say it is an object...

An item of a type tracked in sys.objects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, and rules.

Answer 1

According to the Documentation for sp_rename

renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view

And also in the next paragraph:

Renaming an object such as a table or column will not automatically rename references to that object. You must modify any objects that reference the renamed object manually. For example, if you rename a table column and that column is referenced in a trigger, you must modify the trigger to reflect the new column name

The [sys].[sql_modules] view documentation says that:

Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view

Looking at the definitions at sys.objects.[type], we see

• P = Stored Proc
• RF = Replication-filter-proc
• V = View
• TR = Trigger
• FN = SQL scalar function
• IF = SQL inline table-valued function
• TF = SQL table-valued-function
• R = Rule (old-style, stand alone)
• D = Default

Notice how each of these are pre-scripted sql statements.

We also know that sp_rename renames associated indexes and their PRIMARY constraints and can also rename primary and secondary XML indexes.

Basically, the database must enforce integrity and being able to rename a column without also renaming the dependent columns would utterly break the database.

Thus, it makes perfect sense that sp_rename would do what you noticed.