CODEWITHSUNDEEP
sql-serversql-server-2008rename
JoshuaD
JoshuaD

Reputation: 3

How to rename a table name that has a hyphen in it - SQL Server 2008 R2

I have a table in my database that's showing up as "revised-alert" (with the double quotes). It's become apparent that hyphens should not be used in table names, but I've learned my lesson but would like to move forward with renaming it or getting the data from it and dropping it.

I'm getting the following errors when trying to rename the table and querying the table:

RENAME ERROR MESSAGE:

ERROR: No item by the name of 'DB_12345_dev.dbo."revised-alert"' could be found in the current database 'DB_12345_dev', given that @itemtype was input as '(null)'. Error Code: 15225

exec sp_rename
  @objname = 'DB_12345_dev.dbo."revised-alert"',
  @newname = 'revisedalert'

QUERY ERROR MESSAGE:

ERROR: Invalid object name 'DB_12345_dev.dbo.revised-alert'. Error Code: 208

select *
from DB_12345_dev.dbo."revised-alert"

Upvotes: 0

Views: 932

Answers (3)

emoreau99
emoreau99

Reputation: 674

Have you tried the following:

exec sp_rename @objname = 'DB_12345_dev.dbo.[revised-alert]', @newname = 'revisedalert'

Upvotes: 0

Afnan Yousuf Ali
Afnan Yousuf Ali

Reputation: 7

If you just want to get data from this table so you can do it with simple

select * from "revised-alert"

OR It is better to create a new table with all the data from "revised-alert" using this

select * into NewTable from "revised-alert"

Upvotes: 0

Dan
Dan

Reputation: 5231

I created a "revised-alert" table in my test instance, then ran profiler to capture the command executed if I renamed the table through right click -> rename in SSMS. It looks like you may need to add object delimiters [ ] around the name that includes the quotes.

/* create a test table for demonstration purposes */
select 1 as val into dbo.["revised-alert"]

/* select to show table exists */
select * from dbo.["revised-alert"]

EXEC dbo.sp_rename @objname = N'[dbo].["revised-alert"]', @newname = N'revisedalert', @objtype = N'OBJECT'

/* select from renamed table */
select * from dbo.revisedalert

Upvotes: 1

Related Questions