Lets say you’ve changed the name of your physical server and you want your sql server instance name to reflect this.

--THIS WILL SHOW YOU THE SERVERS 
select *
from sys .servers

--THIS WILL DROP THE SERVER FROM THE SERVER TABLE 
exec sp_dropserver 'OLD_SERVER_NAME'

--THIS WILL ADD THE NEW SERVER TO THE SERVERS TABLE, LOCAL WILL MAKE THIS SERVER_ID 0, THE INSTANCE YOU'RE ON
exec sp_addserver 'NEW_SERVER_NAME', 'local'

 

It goes without saying, be careful. Think carefully about what you’re dropping and adding 😉