[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1225
  • Last Modified:

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

Greetings.

I want change all the Running no from
                      [Rno] [numeric](18, 0) IDENTITY (1, 1) NOT NULL                              
                       to
                      [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
) ON [PRIMARY]
GO

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


0
fortuna_techmy
Asked:
fortuna_techmy
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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)
AS
BEGIN
     
     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

END
GO


Exec sp_MSFOREachTable "Exec ChangeIdentityColumns '?' "
0
 
imran_fastCommented:
> 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'
RECONFIGURE WITH OVERRIDE


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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now