sp_addserver - Where does it add in which system table. if I use this in my current server configuration will it change the name of my server and how will it affect ?
sp_addserver is for adding servers - for anything other than the local server, it is adding a linked server. It puts server information in the catalog view (not system table)
sys.servers. It does not affect the name of your current server unless you are actually using it to run
sp_addserver after you have already renamed the underlying server (which you do to ensure functions like
@@SERVERNAME return the right value) - this should be a rare incident.