• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

identity column (SQL2K)

I HAVE A TABLE AS BELOW WITH IDENTITY COLUMN SET

CREATE TABLE FADEBTP (
  FAFYNE  CHAR     (3)      NOT NULL,
  FAEYNE  NUMERIC  (11)     IDENTITY (1, 1)      NOT NULL,

I NEED TO DROP THE IDENTIY COLUMN WHICH IS SET ON   FAEYNE

PLS WRITE A SQL SCRIPT TO DROP IT
0
Richardsoet
Asked:
Richardsoet
  • 3
  • 2
  • 2
  • +2
1 Solution
 
adatheladCommented:
I think you have to do this within Enterprise Manager by unchecking the IDENTITY option in the Design View of the table.
Using the ALTER TABLE...ALTER COLUMN statement doesn't appear to drop the IDENTITY option
0
 
RichardsoetAuthor Commented:
I KNOW I CAN DROP IT IN eNTERPRISE Manager , but i actally need a  TSQL script to do it
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Richardsoet - would you mind closing some of your existing questions?  Including this question, you have 10 questions open, several of which have either been answered or need your participation to continue.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
adatheladCommented:
I've had a search and found this: http://www.experts-exchange.com/Databases/Q_20829246.html
(thought I'd remembered seeing this crop up before!)

Basically, you cannot simply drop the IDENTITY option. You have to create a new column, copy all the existing identity column values into this new field, drop the existing IDENTITY column and then rename the new column.

0
 
RichardsoetAuthor Commented:
i have ask this question more than ten times , that how do i close down calls , no one get back to me , i will be happy if you can tell me the procedure to follow to close down calls thanks
0
 
adatheladCommented:
To close a question, please see here: http://www.experts-exchange.com/help.jsp#hi9

Thanks
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
If you do not have an answer, don't close the question, but there are 6 questions where other experts have responded after you and you have not given any feedback regarding their comments.  They can't help you unless you work with them.  In addition, there are at least 2 questions that appear to be answered.

Awaiting comments from you:
http://www.experts-exchange.com/Web/Application_Servers/Websphere/Q_21349518.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21401316.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21414990.html
http://www.experts-exchange.com/Databases/Q_21418144.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21418151.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21407235.html

Appear to be answered:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21400982.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21401031.html

How to close questions:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi68

Rather than asking a question over and over, please post 20 point pointer questions to the original question.
0
 
david_chiuCommented:
Hi, Richardsoet,

Here is the SQL scrip for your review, hope it will help you!

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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
CREATE TABLE dbo.Tmp_FADEBTP
      (
      FAFYNE char(3) NOT NULL,
      FAEYNE numeric(11, 0) NOT NULL
      )  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.FADEBTP)
       EXEC('INSERT INTO dbo.Tmp_FADEBTP (FAFYNE, FAEYNE)
            SELECT FAFYNE, FAEYNE1 FROM dbo.FADEBTP TABLOCKX')
GO
DROP TABLE dbo.FADEBTP
GO
EXECUTE sp_rename N'dbo.Tmp_FADEBTP', N'FADEBTP', 'OBJECT'
GO
COMMIT

Best Regards,
David
0
 
Anthony PerkinsCommented:
>>I KNOW I CAN DROP IT IN eNTERPRISE Manager , but i actally need a  TSQL script to do it <<
david_chiu has demostrated how you can do this with Enterprise Manager. Just look for the "Save change script" icon.

P.S. Please lose the ALL CAPS it looks like you are yelling.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now