June 21, 2012 at 3:34 pm
I have a table with a PK called '[ID]'.
I want to rename it so that it is without brackets.
How to do it?
I tried two methods:
(1)
sp_RENAME PortfolioMapping.[ID] , ID, 'COLUMN'
go
getting error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
(2)
ALTER TABLE PortfolioMapping RENAME column [ID] TO ID
getting error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'RENAME'.
DDL
/****** Object: Table [dbo].[PortfolioMapping] Script Date: 6/21/2012 2:33:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PortfolioMapping](
[[ID]]] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
PRIMARY KEY CLUSTERED
(
[[ID]]] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--Quote me
June 21, 2012 at 3:43 pm
Try this script:
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
EXECUTE sp_rename N'dbo.PortfolioMapping.[[ID]]]', N'Tmp_ID', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.PortfolioMapping.Tmp_ID', N'ID', 'COLUMN'
GO
ALTER TABLE dbo.PortfolioMapping SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply