July 13, 2004 at 11:27 am
Is there a way to add a column in the middle of a table or even to the fron of a table? I know the SQL Server Enterprise Manager allows me to do this but I am asking if there it a T-SQL method.
Thank you.
July 13, 2004 at 11:29 am
You have to recreate the table and that is how EM does.
July 13, 2004 at 11:50 am
may I ask you why do you need that?
You could create view on the underlying table where you could specify the order of the columns the way you like.
July 13, 2004 at 12:35 pm
In short, I want to add a new column at the front of every table within the database. There is hundreds of tables and it would take me a very long time to do it through Enterprise Manager. Therefore, I was hoping there was scripts or predefined procedures that would do this for me. I was hoping to short cut creating a script myself.
July 13, 2004 at 1:20 pm
Why does it have to be in the front?
If you are looking at creating some sort of ID column or other information you can just ensure that in your SELECT or VIEW that you list the column 1st...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 13, 2004 at 1:25 pm
Its only a preference within my company. It will be the first column no matter how I do it. Developers have direct access to the development databases and we want them to always be aware of this new column. Many of our developers use EM to view structure and data in development databases. Therefore, it will be the first column. I am just looking for the easiest method of accomplishing the task but it appears there is no set script or sp.
July 13, 2004 at 3:09 pm
SELECT 'SELECT ' + CHAR(39) + 'TEXT_COLUMN' + CHAR(39) + ' AS NEW_COL_NAME, * ' + CHAR(13) + 'INTO [' + TABLE_NAME + '_TEMP] ' + CHAR(13) + 'FROM [' + TABLE_NAME + '] ' + + CHAR(13) + 'GO ' + CHAR(13) + 'DROP TABLE ['+ TABLE_NAME + '] ' + CHAR(13) + 'GO ' + CHAR(13) + 'SELECT * INTO [' + TABLE_NAME + ']' + CHAR(13) + 'FROM [' + TABLE_NAME + '_TEMP] ' + CHAR(13) + 'GO ' + CHAR(13) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
This is just a primitive script. It won't work if you have PK/FK and you'll lose all your indexes and triggers.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
July 14, 2004 at 1:52 am
Why not just write a stored proc that takes a table name as a parameter and then does what EM does:
1. Make a note of the table's metadata
3. Copy the table into a temp table
3. Drop the table
4. Recreate the table with the added column on the front of it using the metadata captured earlier
5. Copy the data back from the temp table
Regards
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
July 15, 2004 at 3:44 am
It is easy to see the script created by Enterprise Manager.
Open a sample table, make the changes and then click on 'Save Changes Script'
See example below:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
NewColumn char(10) NULL,
Code numeric(18, 0) NOT NULL IDENTITY (1, 1),
Column1 char(10) NULL,
Column2 char(10) NULL,
Column3 char(10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (Code, Column1, Column2, Column3)
SELECT Code, Column1, Column2, Column3 FROM dbo.Table1 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
Code
) ON [PRIMARY]
GO
COMMIT
July 15, 2004 at 6:11 am
StefanJ's posting is excellent. However, I need the behind the scene stuff that Enterprise Manager used to create the script. Then I could add a cursor to get every table within my database and then run the Enterprise Manager's behind the scene stuff in a loop.
July 15, 2004 at 6:33 am
I'm guessing all it does is query the INFORMATION_SCHEMA views which shouldn't be too hard to replicate!
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
July 15, 2004 at 8:10 am
Actually, it probably is pulling from the sysobjects table. You'll have to figure out how to read that yourself.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
July 15, 2004 at 9:03 am
DROP TABLE tblRichTest1
-- Create a table
CREATE TABLE tblRichTest1(
Column1 VARCHAR(100),
Column2 VARCHAR(100)
)
GO
-- Add a column
ALTER TABLE tblRichTest1
ADD RichTestID INT IDENTITY
GO
-- Allow updates to system tables
EXEC sp_Configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
-- Now move that column to the start of the table by altering the ordinal position of rows on target
DECLARE @OrdinalPosition INT,
@TableName VARCHAR(128),
@ColumnName VARCHAR(128)
SET @OrdinalPosition = 1 -- The position we want the column in
SET @TableName = 'tblRichTest1'
SET @ColumnName = 'RichTestID'
-- Check if the required Ordinal Position is available
IF EXISTS (SELECT id FROM sysColumns WHERE object_name(id) = @TableName AND ColID = @OrdinalPosition)
BEGIN -- Need to bump subsequent columns along one
-- Increment all columns greater than the correct position of inserted column
UPDATE sysColumns
SET ColID = ColID + 1
WHERE OBJECT_NAME(ID) = @TableName
AND ColID >= @OrdinalPosition
END
-- Make the ID column of the inserted column correct
UPDATE sysColumns
SET ColID = @OrdinalPosition
WHERE OBJECT_NAME(ID) = @TableName
AND Name = @ColumnName
-- Sync Column Order
UPDATE sysColumns
SET ColOrder = ColID
WHERE OBJECT_NAME(ID) = @TableName
GO
-- Prevent updates to system tables
EXEC sp_Configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply