Thursday, November 30, 2006

SQL Server: How to rename an index

When you create a table with a primary key in SQL Server, SQL Server creates an index for you with an automatically generated name, like PK__acctreg__29819341. I find the naming convention they use to be annoying, so I wanted to be able to change the name. Here's how:

-- to find the names of your current indexes
use my_database
select table_name, index_name
from sysobjects tab
left join sysindexes idx on =
where tab.xtype = 'U'
order by 1

-- to rename an existing index
exec sp_rename 'company.PK__company__1ED599B2', 'company$companyID', 'INDEX'

1 comment:

  1. worth noting if you have funky characters in your index names (eg dots) you need to wrap with [ ] brackets.

    exec sp_rename '[]', '', 'index'


I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.