How disable IDENTITY on column

Hello,

I have big problem with IDENTITY column in table. I must disable this function in 500 tables in my system and i don`t know how do this :( is it such way in order to do this ?

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO

-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO|||Yes I know this manner, but I can`t disable IDENTITY on all tables because system say me that I can use this function only one table in this moment and don`t allow me disable IDENTITY on tables.|||

something to start with

use northwind

select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'

BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
END

ROLLBACK TRANSACTION
SELECT * FROM #ALLTABLES

rollback transaction

|||

I am not clear on what you want to do. Do you want to disable it temporarily on multiple tables? Or eliminate it permanently? I can whip up the basis of a routine to change the identity column to no longer be an identity column, but from a further reply that doesn't seem to be what you want.

You can only use SET IDENTITY_INSERT ON on only one table at a time, per connection, but this should be acceptable because you can only insert into one table at a time per connection. So if this is a temporary thing, then all you need to do is just turn it off for the table you are working on.

Expand and a better answer can possibly be arrived at.

|||I understand. :( it`s a pity that we can`t disable this function on all table on one moment... thanx for help|||

hey take a look at this

use northwind
set xact_abort off
select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'

BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
END

it says 33 rows affected

|||Try this:

DECLARE @.Statement nvarchar(2000)
DECLARE Statements CURSOR LOCAL FAST_FORWARD
FOR SELECT N'SET IDENTITY_INSERT ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME) + ' OFF'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN Statements
FETCH NEXT FROM Statements INTO @.Statement
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Statements INTO @.Statement
BEGIN TRY
EXEC(@.Statement)
END TRY
BEGIN CATCH
SELECT 'Error:'+ERROR_MESSAGE()+' on:'+@.Statement
END CATCH
END
-- Clean up work
CLOSE Statements

If you are sure that all the tables have identity columns you can use this:

sp_msforeachtable @.command1="print '?'", @.command2="SET IDENTITY_INSERT ? OFF"