reordering column names in table easily 2

[elallred]

We had an outside "consultant" who created a table with over 500 columns. These column names seem to be arranged randomly --there is no discernable order to them. I would like to reorder the names in alphabetical order. Is there an easy way to do this?

 

[dbmsguy]

one way I might consider would be to:

1. bcp the table out with the 'queryout' option: ex bcp "select a,b,c,d from tab" queryout c:\myfile -T -c
2. either drop/redefine or create a new table in the order you want
3. bcp back in.....


should do the trick. Paul

http://www.dbmsguy.com

 

[rajeevnandanmishra]

Hi elallred,
Following procedure accepts three parameters:
1.sourcetable
2.destinationtable (if null then procedures return the resultset otherwise it creates the destinationtable. Make sure that the destination table is not already exist.)
3.orderby (accepts ASC or DESC. default is ASC)

create procedure ordercolumns
(@sourcetable varchar(30), @destinationtable varchar(30)=null,
@orderby varchar(4)='ASC') as
declare @strsql varchar(255)
if @orderby='ASC'
begin
select name into #temp_ordercol_ASC from syscolumns
where obje
ct_name(id)=@sourcetable order by name
select @strsql='select '
select @strsql=@strsql+name+', '
from #temp_ordercol_ASC
drop table #temp_ordercol_ASC
end
else
begin
select name into #temp_ordercol_DESC from syscolumns
where object_name
(id)=@sourcetable order by name DESC
select @strsql='select '
select @strsql=@strsql+name+', '
from #temp_ordercol_DESC
drop table #temp_ordercol_DESC
end
if @destinationtable is null
select @strsql=substring(@strsql,1,len(@strsql)-2)+
'
from '+@sourcetable
else
select @strsql=substring(@strsql,1,len(@strsql)-2)+
' into '+@destinationtable+' from '+@sourcetable
exec(@strsql)
return

Execute this procedure as :
EXEC ordercolumns 'myCurrentTable','myNewTable','ASC'

You should make changes as per requirements.
Hope it works.

 

[markSaunders]

is there an easy method to replicate the triggers etc that may be associated with these tables? Mark Saunders

 

[SQLSister]

Script the triggers then you can run the scripts against the new tables. Actually, you also need to drop the old tables and rename the new ones with the old name to keep from breaking the user interface and all the stored procedures. But don't do this until you are sure the tables converted correctly. In fact if you really feel you must do this (and I believe it is a totally unnecessary exercise), don't do it without running a complete backup first!

I am actually more concerned about the 500 columns part than the fact they aren't in order. While SQL server will bviously take that number of columns, unless they are very small in length (or none are of a variable size), you could have a problem with people trying to add records that are bigger than the maximum size a record can be. From Books Online on this subject:

SQL Server can have as many as two billion tables per database and 1,024 columns per table. The number of rows and total size of the table are limited only by the available storage. The maximum number of bytes per row is 8,060. If you create tables with varchar, nvarchar, or varbinary columns in which the total defined width exceeds 8,060 bytes, the table is created, but a warning message appears. Trying to insert more than 8,060 bytes into such a row or to update a row so that its total row size exceeds 8,060 produces an error message and the statement fails.

 

[markSaunders]

of course - the script will exist - is there a simple sp to drop all triggers associated with a table - this could be appended to the orderColumns proc - along with a drop table and, as you say, the trigger scripts

Mark Saunders