alter table TB alter column rNo    IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL

Posted on 2006-04-28
Last Modified: 2008-01-09

I want change all the Running no from
                      [Rno] [numeric](18, 0) IDENTITY (1, 1) NOT NULL                              
                      [Rno] [numeric](18, 0) IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL

2 problems :
a) I cannot use ALTER TABLE to change it to     IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL. Note all the tables already have records
b) All the tables need to be changed.  I need to use a while loop to go thru all the tables (excluding system tables)  and update to   [Rno] [numeric](18, 0) IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL

Thank you.

CREATE TABLE [dbo].[test] (
      [Rno] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [icno] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

alter table test alter column    
[Rno] [numeric](18, 0) IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL

Question by:fortuna_techmy
    LVL 142

    Accepted Solution


    you cannot alter the table in this way. you have to drop and recreate the column.

    to save the values, copy the table data,
    recreate the table as needed, and copy back the data (with the SET IDENTITY_INSERT ON (test)  to keep the identity values)
    and finally drop the copy table
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    You can't alter an identity column, you need to drop and recreate it ..
    Create the procedure as shown below (its a pseudocode)

    create a procedure ChangeIdentityColumns @TableName(varchar(2000)
         DECLARE @sql varchar(2000)
         SET @Sql = ' ALTER TABLE  '+@tableName +' ADD COLUMN NewI int ;' +
                           ' UPDATE '+@TableName +' SET NewI = I  ;' +
                           '  ALTER TABLE  '@tableName +' DROP COLUMN i ;' +
                            ' ALTER TABLE  '+@tableName +' ALTER  COLUMN NewI int  identity NOT FOR REPLICATION ;'
        Exec sp_executeSQL @SQL


    Exec sp_MSFOREachTable "Exec ChangeIdentityColumns '?' "
    LVL 28

    Assisted Solution

    > All the tables need to be changed

    Take the backup of your database.
    configure your database to allow update on system tables and then update syscolumns table.


    EXEC sp_configure 'show advanced option', '1'

    EXEC sp_configure 'allow updates', '1'

    update syscolumns set colstat =9 where
    name ='Rno'
    id in (select id from sysobjects where name ='TB')

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now