SQL Server Performance Forum – Threads Archive

Identity constraint

Can I remove Identity constraint from one column in my table? Pls suggest me code. Regards,
Ashish Ashish Johri

---

SET IDENTITY_INSERT sales_item OFF can’t be the answer of my question. I want to remove this constraint permanently. Ashish Johri

---

alter table sales_item drop IDENTITY(1,1,1) is not working. Ashish Johri

---

Identity is not a constraint, it’s a property. There doesn’t appear to be any simple syntax to remove the property. Add another INT column to the table, add a unique constraint if necessary.
Copy the identity value into the new column.
Drop the identity column (if you have FKs referring to this column, drop those FKs first).
Rename the new column (and recreate any FKs you dropped in the previous step).

---

Thanks Adriaan, Actually I copied data from one DB to another in the same table.
Eg. From DB1.dbo.Sales to DB2.dbo.Sales.
I used Micorsoft#%92s export data utility. I did the same you told and I could successfully transfer the data.
Thanks for that. But this time one problem cropped up. Now I have table and column sales_id populated in this. I want to make this an identity column.
I created one new column temp and made this IDENTITY. if not exists(
select 1 from sysobjects o, syscolumns c
where o.id = c.id
and o.name = ‘Sales’
and c.name = ‘temp’) alter table Sales add temp int IDENTITY(1,1) Now I copied all the values from sales_id to that temp. Update Sales set temp = Sales_Id And I delete original sales_ID column.
This is successful. Now when I rename this column—->as follows sp_rename ‘tablename.columnname’, ‘newcolumnName’,’column’
I get an error. "Msg 15248, Level 11, State 1, Procedure sp_rename, Line 213 Either the parameter @objname is ambiguous or the claimed @objtype (column) is wrong." I could not make out what this error is. Please any pointer?
Ashish Johri

---

The easy way out is of course to do the renaming in Enterprise Manager, so you don’t have to worry about the exact syntax.

---

You are getting the error while checking column existence in the code…
— check for wrong param
if ((@colid is not null AND @objtype <> ‘column’) OR
(@colid is null AND @objtype = ‘column’))
begin
COMMIT TRANSACTION
raiserror(15248,-1,-1,@objtypeIN)
return 1
end Try qualifying schema name as an example in BOL… exec sp_rename ‘schemaname.tablename.columnname’, ‘newcolumnName’,’column’ MohammedU.
Moderator
SQL-Server-Performance.com

---

Thanks Mohammed and Adriaan for support. Regards,
Ashish Ashish Johri

---

How did you rename your column?
Using…Alter table command or SSMS? MohammedU.
Moderator
SQL-Server-Performance.com

---

Sir I renamed column by using the code as below: sp_rename ‘schemaname.tablename.columnname’, ‘newcolumnName’,’column’
Ashish Johri

---

]]>