In SQL Server, you can use the sp_rename stored procedure to rename an object, including a table.

Most of the other major RDBMs allow you to rename a table with the ALTER TABLE statement, but this isn’t the case with SQL Server.

Example

Here’s an example to demonstrate:

EXEC sp_rename 't1', 't2';

This renames the table called t1 to t2.

Including the Schema Name

You can also qualify the first table with the schema name, in which case, it might look something like this:

EXEC sp_rename 'dbo.t1', 't2';

In this example, dbo is the schema name, but you will need to use whatever schema is applicable.

Including the Parameter Names

As with any stored procedure, you can also include the parameter names when calling sp_rename:

EXEC sp_rename 
    @objname = 'dbo.t1',
    @newname = 't2';

The sp_rename procedure also accepts an @objtype parameter, but this is not required (or supported) when renaming tables.

Check for References

When you rename a table in SQL Server, you’ll probably see a message like this:

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

This is because when you rename a table, SQL Server does not automatically rename any references to that table. This is also true when you rename a column.

Despite the above cautionary message, the table is renamed anyway.

Therefore, before you rename any tables, you should always check for scripts and stored procedures that reference that table. You will need to update such scripts and procedures to reference the new table name.

You can use the sys.sql_expression_dependencies system catalog view to do this check.

Example:

SELECT OBJECT_NAME(referencing_id) AS [Referencing Entity],   
    o.type_desc AS [Type],
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS [Column],
    referenced_entity_name AS [Referenced Entity],
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS [Column]
FROM sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
WHERE referenced_id = OBJECT_ID(N't1');

In this case I joined it with sys.objects to return more information.

Here’s the result I get before I changed the name of the t1 table:

+----------------------+----------------------+----------+---------------------+----------+
 | Referencing Entity   | Type                 | Column   | Referenced Entity   | Column   |
 |----------------------+----------------------+----------+---------------------+----------|
 | usp_t1               | SQL_STORED_PROCEDURE | (n/a)    | t1                  | (n/a)    |
 | vt1                  | VIEW                 | (n/a)    | t1                  | (n/a)    |
 | t1                   | USER_TABLE           | c2       | t1                  | c1       |
 +----------------------+----------------------+----------+---------------------+----------+ 

This shows me that there’s one view, one stored procedure, and a computed column that depend on the t1 table. The computed column (c2) references the c1 column in the same table.

As mentioned, it’s important to run this check before you change the name of the table. Here’s the result I get when running the same script after changing the name of the table.

SELECT OBJECT_NAME(referencing_id) AS [Referencing Entity],   
    o.type_desc AS [Type],   
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS [Column],
    referenced_entity_name AS [Referenced Entity],   
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS [Column]
FROM sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
WHERE referenced_id = OBJECT_ID(N't1');

Result:

(0 rows affected) 

In this case, I used the table’s original name (t1), so it’s quite obvious that we didn’t even target the correct table (t2).

Simply changing this script to reference the new table name won’t work either. For example, running the following script after we’ve changed the name of the table from t1 to t2 returns just one dependency (the computed column).

SELECT OBJECT_NAME(referencing_id) AS [Referencing Entity],   
    o.type_desc AS [Type],   
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS [Column],
    referenced_entity_name AS [Referenced Entity],   
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS [Column]
FROM sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
WHERE referenced_id = OBJECT_ID(N't2');

Result:

+----------------------+------------+----------+---------------------+----------+
 | Referencing Entity   | Type       | Column   | Referenced Entity   | Column   |
 |----------------------+------------+----------+---------------------+----------|
 | t2                   | USER_TABLE | c2       | t2                  | c1       |
 +----------------------+------------+----------+---------------------+----------+ 

The good news is that the computed column is returned. The bad news is that the view and stored procedure are not returned.

Bottom line: Check for dependencies before you rename the table. Then manually update any objects that reference the renamed table.