How toenable identity insert in a dabase level

Question

How toenable identity insert in a dabase level

sbalaven

Ten Centuries

Points: 1127
More actions
July 18, 2008 at 4:39 am

#188300

Hi,
Is it possible to set the identity column allow insert in a database level?
In BOL it says only in table level we can set identity insert.(Set Identity_Insert on)
But i need to set that in Database level for temporarily when i doing data refresh from other environments.
Regards,
S.Balavenkatesh

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Identity insert has to be set per table, and may only be on for one table at a time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sbalaven Ten Centuries Points: 1127 More actions · Answer 2

How to find out how many tables were having identity column.

Is it simple query to find out all the table name

Regards,

S.Balavenkatesh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

SELECT OBJECT_NAME(object_id), name FROM sys.columns WHERE is_identity = 1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sbalaven Ten Centuries Points: 1127 More actions · Answer 4

I tried in SQL 2000 version. But it is not working.

Can you please send in sql 2000 version query.

Bala

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sbalaven (7/18/2008)
I tried in SQL 2000 version. But it is not working.

You posted in the 2005 forums, hence I assumed you are using SQL 2005.

Please use the appropriate forum in the future.

Can you please send in sql 2000 version query.

SELECT OBJECT_NAME(id), name

FROM syscolumns

WHERE COLUMNPROPERTY ( id , name , 'IsIdentity') =1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sbalaven Ten Centuries Points: 1127 More actions · Answer 6

Hi,

Now it is working. Thanks for your help

Regards,

S.balavenkatesh

sbalaven Ten Centuries Points: 1127 More actions · Answer 7

Hi One more clarifications

Is there any query to find what is the status of that identity column in the table whether it is identity_insert on / off status.

Regards,

Bala

Nabha SSCrazy Eights Points: 8534 More actions · Answer 8

Hi,

I think u can use this query as well to find out the identity column.

SELECT object_name(id), name

FROM syscolumns WHERE status = 0x80

Regards,

Prakash.P

---------------------------------------------------------------------------------

sbalaven Ten Centuries Points: 1127 More actions · Answer 9

Hi

It shows as list of tables and their identity columns.

My quentions is if i set the identity column as allow insert explicitly. After that how can i find what are the tables identity column identity_insert on

Bala

Nabha SSCrazy Eights Points: 8534 More actions · Answer 10

hi,

SELECT object_name(id),name, COLUMNPROPERTY(id,name, 'IsIdNotForRepl') as I_INSERT

FROM syscolumns WHERE status = 0x80

If the I_Insert is 1 then Identity_Insert is not checked.

Prakash.

---------------------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Identity not for replication is not the same as Identity insert

sbalaven: identity insert is set per session and may only be enabled on one table at a time. So if you in your current session have not enabled identity insert on any table, then it is not on. When you do switch it on, make sure that you switch it off immediatly after finishing the insert, as switching identity insert on for a second table will result in an error.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sbalaven Ten Centuries Points: 1127 More actions · Answer 12

My question is, is there any query to find out the current status of the identity column. whether the identity_insert is on or off

Bala

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

sbalaven (7/18/2008)
My question is, is there any query to find out the current status of the identity column. whether the identity_insert is on or off

No, because it's not a property of the column or table. As I said, it's a session specific setting. If you set it on for a table, you and only you will be able to insert into the identity column. Another user will not.

The way to know if Identity insert is on for you for a specific table is to keep track in your script of whether or not you have set it on.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sbalaven Ten Centuries Points: 1127 More actions · Answer 14

sbalaven

Ten Centuries

Points: 1127

July 18, 2008 at 7:40 am

#845037

Ok Thanks for your help

Balavenkatesh