If you want to rename a table in TSQL. We can use the built-in function called sp_rename. This function will accept two parameters string. The first one will be the table name that you want to rename. The second parameter string will be the new table name. In order to be able to rename the table. You are required to be the owner of the database.

Let's say we have a table named Users and we want to rename it to NewUsers.

EXEC sp_rename 'Users', 'NewUsers'

It is normal to get the following warning message "Caution: Changing any part of an object name could break scripts and stored procedures." 

What this one means is if you have any other script or stored procedure that references back to the Users table. You are required to modify the reference name manually. The replacement will not be done automatically against any table reference used in scripts.