Adding Column to a Table with Desired Order
-
I'm trying to add a column to a table through script, but I don't want it to be at the end of the table. It's part of the primary key and I want it to be the 2nd column in a table.
I think if I manipulate syscolumns table I can get the desired result, but I need to do it for multiple tables within a db. Does anyone has the script that accomplishes this and covers both SQL 7 and 2000?
Thank you
-
Don't even think about changing syscolumns UNLESS you need practice at restoring your database. Yes, this has the column order but there is no doco on how SQL Server is using it. If you have any problems, you will not get any help. Changing system tables is not a good idea unless you are quite sure about what you are doing.
There is not easy way to adjust the column order. The only reason for putting the new column second in the list of columns is to help you. The primary key will function the same regardless of the position of the columns in the table.
If you want to control the column order, you will need to re-create the table. This is exactly what happens when you add the column when making changes to a database diagram. You can easily get the script that is generated when making changes this way. Just click on the"clipboard" icon ("save change script") and follow the bouncing ball.
-
ikorenbl,
Try the next setup;
select <re-arrange your columns>
into #temp_table
from original_table
drop original_table
select *
into original_table_remake
from #temp_table
Recreate PK's ,Indexes and others stuff
Just a thought on this...
GKramer
The Netherlands
-
The position of a column in a table is completely irrelevant for the relational engine. Just a matter of "good looking". There are also very few implications on the storage engine, but they are also negligible.
If you insist on your wish, presumably the easiest solution is to do this via EM (and not having a look at what EM is doing behind the scenes).
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I realize position doesn't affect anything and EM is not an option for me.
Here is a script I found that reorders columns in sequential order. I thought someone has the script to do more.
How to Fix Inconsistent Metadata Errors
Why does changing a table on a SQL Server 7.0 server cause "OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time." to occur when query the table from a SQL Server 2000 server via link to other.
BASIC UNDERSTANDING OF THE PROBLEM:
OK here is what I discovered. In SQL Server 7.0 when a column is deleted, the syscolumns table keeps the original ordinal position listed instead of adjusting to what the table looks like now.
For example, table 1:
ColName OrdPos
----------- ---
Col1 1
Col2 2
Col3 3
Col4 4
Col5 5
Col6 6
In SQL Server 7.0 when you remove Col3, Col4 a result of Ordinal Position in the syscolumns table would look like this:
ColName OrdPos
----------- ---
Col1 1
Col2 2
Col5 5
Col6 6
SQL Server 2000 though will actually reset the values and the output looks like this
ColName OrdPos
----------- ---
Col1 1
Col2 2
Col5 3
Col6 4
OK when you link from SQL Server 2000 to SQL Server 7.0 the link will run several request one being
sp_columns_rowset N'yourTblNamehere', N'dbo', NULL
and it apparently reads the ORDINAL POSITION column which when is first built is in order by when you delete items it is no longer with SQL 7 so it outputs
THE ERROR MESSAGE:
Server: Msg 7353, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
WHAT I DID:
(note: testing was done on all versions of SQL Server 7.0 with each SP level thru 3)
First I opened Profiler and connected to the remote server to watch the calls being made and looked at the items it ran, testing each until I saw sp_columns_rowset as the only column related item. Then running I noticed the ORDINAL POSITION output was not consecutive which made me curious so I deleted and re-added the table with the final version the table was in and ran the process again. This time the ORDINAL POSITION output was consecutive and when I ran my query from the other server it worked fine. So I removed another column from before then end of the table and ran query again and got output again. Then I went back and look at the ORDINAL POSITION output of sp_columns_rowset and again not consecutive. So I decided to try under SQL 2000, and noticed that whenever I changed the columns that the ORDINAL POSITION reset and remained consecutive. Now I decided must be that output for is the cause of the error and decided to manually change the values in the syscolumns table myself. Ran my remote query again, which ran without error.
OK so I know the problem and potential how to fix, but the fix is a bit of a headache. That is where the following came from. I decided the best way to quick fix was to build a process I can run to handle it quickly for me. To use just load into master (as you will probably use it often to clean
up) and run like so:
USE YourDBHere
GO
sp_FixColOrder YourTableNameHere
GO
OR
YourDBHere..sp_FixColOrder YourTableNameHere
Anyway here is the fix code and I will send this entire message to Microsoft to follow up on. Hopefully they will provide a better fix for this in SP4 if I can get in soon enough.
THE PROCEDURE:
-------------------------------------Procedure Begins Here----------------------------------------
/*
* Procedure Name: sp_FixColOrder
* Full Name: Fix Column Order
* Desc: Fix column order listing in syscolumns table for specified table.
* Notes: For use with SQL 7 only, not tested elsewhere. Procedure makes use of
* changing value in system table which can cause issues and even though
* I have tested I make no guarantees on the effects of this procedure.
*/
CREATE PROCEDURE sp_FixColOrder
@table sysname
AS
DECLARE @SQLState VARCHAR(2000)
--Configure server to allow ad hoc updates to system tables
EXEC master.dbo.sp_configure 'allow updates', '1' RECONFIGURE WITH OVERRIDE
/*Build string to update object, the only reason I build a string is the allow updates exec does not allow straight SQL to occurr.*/
SET @SQLState = 'UPDATE
syscolumns
SET
colid = TruePos,
colorder = TruePos
FROM
syscolumns
INNER JOIN
(SELECT
[name],[id],
colorder, (SELECT COUNT(*) + 1 FROM syscolumns ic WHERE ic.colorder < c.colorder AND ic.[id] = c.[id]) as TruePos
FROM syscolumns c WHERE [id] = OBJECT_ID(''' + @table + ''')
) AS CalcVals ON syscolumns.[name] = CalcVals.[name] AND syscolumns.[id] = CalcVals.[id] AND
syscolumns.colorder = CalcVals.colorder'
EXEC (@SQLState)
--Configure server to disallow ad hoc updates to system tables
EXEC master.dbo.sp_configure 'allow updates', '0' RECONFIGURE WITH OVERRIDE
-
Yeah, as far as I know, the position of the column is irrelevant to the database engine. It only looks good when you have everything grouped together in a data model. I can't remember if you can pull it off in ER Studio, but at least in Visio, you can rearrange the column order to make it look nice. Sorry, I guess that wasn't useful to you at all.
-
SQL Server 2000 though will actually reset the values and the output looks like this
ColName OrdPos
----------- ---
Col1 1
Col2 2
Col5 3
Col6 4
Umh?
create table t
(
c1 int
, c2 int
, c3 int
)
select cast(name as char(30)) as [name], colid from syscolumns where id = object_id('t')
alter table t drop column c2
select cast(name as char(30)) as [name], colid from syscolumns where id = object_id('t')
drop table t
name colid
------------------------------ ------
c1 1
c2 2
c3 3
(3 row(s) affected)
name colid
------------------------------ ------
c1 1
c3 3
(2 row(s) affected)
And I'm on SQL Server 2000. Run this statement and compare it to the output of exec sp_columns 't'. sp_columns will report an ordinal position of 1 and 2. Quite different from the direct query.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I agree with Frank - moving columns by changing syscolumns sounds vaguely suicidal (or is that datacidal?).
You are better off with the solution of copying the data to a temptable, dropping the base table, and then creating it in the order you want, then copying the data back. That is safe, tested, and will not cause any unexpected results.
Quand on parle du loup, on en voit la queue
-
Actually it is an easy and convenient way to fubar SQL Server, IMHO.
Quand on parle du loup, on en voit la queue
Two days ago, I saw Ghostbusters II again, where's that Venkman quote?
And bear with me, since I haven't had French in school (I had Latin
) what does it mean, Patrick?--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
When you talk of the wolf, you will see it's tail.
Laughing - the Venkman quote is from Ghostbuster I -
[Dana has described seeing a terror dog in her refrigerator]
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Auf weiderscriben?
Quand on parle du loup, on en voit la queue
-
When you talk of the wolf, you will see it's tail.
Hui, that sounds dangerous!
Auf Wiederschreiben! Though this phrase does not exist, you might want to stay with the French language
Enjoy your weekend!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I can throw a little Russian into the mix, but let's enjoy the weekend.
Thanks for your replies.
Igor (But in ze Munich Circus I vas ze Incredible Nightcrawler)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply