Add a column with Alter Table
-
Hey guys, just a real quick one here that I think I already know the answer to...
Is there any way to script adding a column to the middle of a table using Alter Table without scripting it out to a temp table, changing/dropping all of my dependencies, dropping the original table and then renaming the temp table to the original table's name?
Thanks.
-
The answer really is no; the right way to do it is to create a new table witht he column in the middle, migrate the data to the new table, drop the old table and rename the new one to the old name, taking care to recreate all the indexes, foreign keys, constraints, etc.
enterprise manager does this behind the scenes when you go to design view of a table.
In theory, you could update syscolumns directly, and change the syscolumns .colid, but i never tried it, and would guess that that would break some indexes at a minimum on the table.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
if u can spare a min pls explain why it is relevant that the column is inserted *in the middle of the table*. whay is it important?
-
it's not terribly important and from a design standpoint it makes no difference, as in the application it can be displayed whereever I want to. It's more of a "for the guy that comes after me" type of thing. I have one column that contains an old format for an inventory number it's the 5th column of a 30 column table. We're changing the way we have in the inventory numbers structured, but they still want to be able to search on both the old and the new. I was going to put them together so that it would be visually easy to see here's the new right next to the old. If you are having issuess, here they are right next to each other, etc.
I just have to change it in quite a few databases(dont' ask long story...) and was planning to script it instead of manually changing them in enterprise manager.
-
you can use Enterprise manager to create the scripts for you;
go to design view, edit the design of the table and notice the little used button at the top named "Save Change Script"
it gives you a preview, and you can save it to file for script distribution.
the only issue is that it uses the names of the constraints to drop , so if each db that the script runs on does not have the same named constraints, it would fail.
in those situations i created a couple of functions that might help:
pass the table/column and it drops all constraints like foreign keys, defaults or constraints tied to the column:
CREATE PROCEDURE DROP_FK_FROM_SPECIFIC_COLUMN
@TableName varchar(30),
@ColumnName varchar(30)
AS
BEGIN
DECLARE @Constraint_to_Delete varchar(100)
DECLARE Constraint_Cursor CURSOR FOR
SELECT name AS ConstraintName FROM dbo.sysobjects
WHERE OBJECTPROPERTY(id, N'IsForeignKey') = 1
and id in
(SELECT constid FROM sysforeignkeys
INNER JOIN syscolumns ON sysforeignkeys.fkeyid = syscolumns.id
WHERE fkeyid IN (SELECT id FROM sysobjects
WHERE name = @Tablename AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
AND syscolumns.name = @ColumnName and fkey =colid)
OPEN Constraint_Cursor
FETCH NEXT FROM Constraint_Cursor INTO @Constraint_to_Delete
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT '+@Constraint_to_Delete
EXEC ( 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT '+@Constraint_to_Delete )
FETCH NEXT FROM Constraint_Cursor INTO @Constraint_to_delete
END
CLOSE Constraint_Cursor
DEALLOCATE Constraint_Cursor
END
GO
CREATE PROCEDURE DROP_DEFAULT_FROM_SPECIFIC_COLUMN
@TableName varchar(30),
@ColumnName varchar(30)
AS
BEGIN
DECLARE @Constraint_to_Delete varchar(100)
DECLARE Constraint_Cursor CURSOR FOR
select c_obj.name as CONSTRAINT_NAME
from sysobjects c_obj
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id
and con.colid = col.colid
where c_obj.xtype='D'
and t_obj.name =@Tablename
and col.name = @ColumnName
OPEN Constraint_Cursor
FETCH NEXT FROM Constraint_Cursor INTO @Constraint_to_Delete
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' ALTER TABLE [' + @TableName + '] DROP CONSTRAINT ['+@Constraint_to_Delete+ ']'
EXEC ( 'ALTER TABLE [' + @TableName + '] DROP CONSTRAINT ['+@Constraint_to_Delete+ ']' )
FETCH NEXT FROM Constraint_Cursor INTO @Constraint_to_delete
END
CLOSE Constraint_Cursor
DEALLOCATE Constraint_Cursor
END
CREATE PROCEDURE DROP_CONSTRAINTS_FROM_SPECIFIC_COLUMN
@TableName varchar(30),
@ColumnName varchar(30)
AS
BEGIN
DECLARE @Constraint_to_Delete varchar(100)
DECLARE Constraint_Cursor CURSOR FOR
select c_obj.name as CONSTRAINT_NAME
from sysobjects c_obj
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id
and con.colid = col.colid
where c_obj.xtype='C'
and t_obj.name =@Tablename
and col.name = @ColumnName
OPEN Constraint_Cursor
FETCH NEXT FROM Constraint_Cursor INTO @Constraint_to_Delete
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' ALTER TABLE [' + @TableName + '] DROP CONSTRAINT ['+@Constraint_to_Delete+ ']'
EXEC ( 'ALTER TABLE [' + @TableName + '] DROP CONSTRAINT ['+@Constraint_to_Delete+ ']' )
FETCH NEXT FROM Constraint_Cursor INTO @Constraint_to_delete
END
CLOSE Constraint_Cursor
DEALLOCATE Constraint_Cursor
END
GO
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
In our work we have a database upgrade script that we run on client sites to ensure that client databases are kept up to date and in a working state.
When we need to ALTER a Column, such as changing it from a NVARCHAR(100) to NVARCHAR(255) we do and ALTER TABLE ALTER COLUMN statement. But having read this thread, you are stating that its best to use the SQL script that Enterprise Manager uses (transfering everything to a TEMP table, drop original table, then create new table with same name, and transfer data into newly created table) - this to me seems very long winded??
Thanks
Tryst
-
Not sure why you don't just let the tools do it for you... if you use EM to make the change, it will do everything for you... no fuss, no muss, no chance of a scripting error.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
But we have databases across many client sites. The upgrade.sql script we have contains all teh legitmate SQL scripts that will bring the client databases to to date.
I would be impossible for someone to remember about 20 changes that we done to a database, and then try and perform them on client site. We just script every change to this upgrade.sql file, and then run that on site.
Thanks
-
Didn't know that and you are correct... usually an ALTER TABLE ALTER COLUMN doesn't require that very long method the EM builds... it is, however, crash proof. So there's a trade off... are you always 100% sure that the alter will not run into some wierd data it can't handle?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
My shop is similar to yours Tristan; we deploy upgrade scripts that add tables or columns, alter columns, migrate data, etc. Those scripts are expected to be performed on lots of databases we don't directly administer.
The issue was if you want the columns in a specific order, you need to rebuild the table; Serg made the excellent point that the order of the columns only matter from a aesthetic perspective, not a functional one.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply