November 18, 2011 at 5:46 am
Hi all,
I have a Table
name int Pk Identity(1,1)
job varchar(20)
how i can remove the Identity property from the column.
well don'nt ask to delete the table or drop the table.- which i know and it is the last option.
all suggestion are well come.
November 18, 2011 at 6:16 am
You can't alter that out of a table. So, you have to create a second table with the structure you want, copy the data across, drop the original table, rename the new table, add any foreign key constraints to the new table.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 18, 2011 at 7:03 am
Drop the column and re-create it.
Here's an example:
CREATE TABLE #test (
id int identity(1,1) PRIMARY KEY,
name nvarchar(50)
);
ALTER TABLE #test ADD new_id int NOT NULL; -- Remember NOT NULL, otherwise you
-- won't be able to set it as the PK later
UPDATE #test SET new_id = id;
DECLARE @name sysname
SELECT @name = name
FROM tempdb.sys.key_constraints
WHERE parent_object_id = OBJECT_ID('tempdb..#test')
DECLARE @sql nvarchar(max)
SET @sql = 'ALTER TABLE #test DROP CONSTRAINT ' + @name
EXEC(@sql)
ALTER TABLE #test DROP column id
EXEC tempdb.sys.sp_rename '#test.new_id', 'id', 'COLUMN'
ALTER TABLE #test ADD CONSTRAINT PK_test PRIMARY KEY(id)
Since I created the example for a #temp table, everything that references tempdb in that script must be changed to your database name.
Hope this helps
Gianluca
-- Gianluca Sartori
November 18, 2011 at 7:04 am
Almost forgotten: wrap that code into a transaction!!!!
-- Gianluca Sartori
November 18, 2011 at 9:09 am
i can do it but
If i have a foreign key related to My Primary key how can i do it
and i don'nt have data in those table it is a empty table
so i just want to remove the db and recreate the db and table by
script the db and edit the required change.
and create every thing new
November 18, 2011 at 9:11 am
Gianluca Sartori (11/18/2011)
Almost forgotten: wrap that code into a transaction!!!!
Less risk when doing that on the test server.
You're going to run that on the test server first? Right?
Then backup prod before running the staement IN transaction.
November 18, 2011 at 11:11 am
Ivan Mohapatra (11/18/2011)
If i have a foreign key related to My Primary key how can i do it ...
By dropping the child table FK... dropping/recreating your Parent table PK... recreating your child table FK pointing to the Parent table perhaps?
Please note you may have to work on your child tables to be sure you have the columns needed to actually build FKs against the new Parent table PK
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 20, 2011 at 5:48 am
The Solution which i found out is
Script the both Pk table And FK table and drop those table and recreate it with one shot .As for this there is a option in the Object explorer > Table > right click> script> Drop And create.
I did IT in this way.
And IT worked .
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply