January 6, 2009 at 3:19 am
Can anybody give me TSQL script to create/alter/remove identity property on existing column and table?
January 6, 2009 at 6:39 am
Create table table1
(
id int identity(1,1) not null,
name varchar(50)
)
i have created table as above script.
Now i want to remove identity on id column using TSQL Script .
Any idea?
January 6, 2009 at 7:42 am
The IDENTITY property and the column it's attached to are inseparable, which means that you can't add or remove IDENTITY from an existing column.You can use workarounds, but these can be very slow, especially with large tables. For example, to add IDENTITY to an existing column, you can create a new table, copy the data from the original table, drop the original table, and then give the new table the original table's name.You can use a similar process to remove IDENTITY from an existing column. Remember, though, that these activities require table downtime—which can be lengthy for large tables—and lots of logging.
MJ
January 6, 2009 at 10:28 pm
MANU (1/6/2009)
The IDENTITY property and the column it's attached to are inseparable, which means that you can't add or remove IDENTITY from an existing column.You can use workarounds, but these can be very slow, especially with large tables. For example, to add IDENTITY to an existing column, you can create a new table, copy the data from the original table, drop the original table, and then give the new table the original table's name.You can use a similar process to remove IDENTITY from an existing column. Remember, though, that these activities require table downtime—which can be lengthy for large tables—and lots of logging.MJ
ok, so is there any query to know on which column identity is created of all tables?
January 7, 2009 at 12:14 am
This will give you a list of identity columns.
SELECT *
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMNPROPERTY -- get columns where is_identity = 1
(
OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)) -- table ID
,column_name
,'isidentity'
) = 1
ORDER BY
table_name
,ordinal_position
Before removing identity properties you will want to be sure applications or other database logic aren't relying on them, like stored procs doing inserts and then capturing the new identity value.
January 7, 2009 at 3:03 am
Eric Klovning (1/7/2009)
This will give you a list of identity columns.
SELECT *
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMNPROPERTY -- get columns where is_identity = 1
(
OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)) -- table ID
,column_name
,'isidentity'
) = 1
ORDER BY
table_name
,ordinal_position
Before removing identity properties you will want to be sure applications or other database logic aren't relying on them, like stored procs doing inserts and then capturing the new identity value.
Thanks a lot
May 29, 2009 at 10:32 am
Thank you. I didn't think this could be done but wasn't sure. Thanks for clarifying it for me. Grady Christie.
September 22, 2009 at 8:16 am
Tx a lot,this also helped on my side, will consider this for future purposes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply