Thursday, February 16, 2012

Changing Field name of an existing Table

Hi,
Is it possible to change any fieldname of an existing table?I mean to say
by TSQL statement.We know that we can alter the data type and width etc.
But I haven't got any info about filedname change.So if it is possible
Please help...
And Is there any TSQL command to alter multiple columns in a single statement?

Please help...
Thanks!!
Joydeepif it is not a primary or a foriegn key you can do something like so...

ALTER TABLE Mytable
ADD INQUIRYID2 BIGINT
GO
UPDATE MyTable SET INQUIRYID2 = INQUIRYID
GO
ALTER TABLE MyTable
DROP COLUMN INQUIRYID

But this a bad idea. What about the existing database object that reference the existing column. Oh well too late. A thousand application buga have just been created. Update your resume. You want biggie size|||if you can connect the db thru EM, just type the new name....|||if you can connect the db thru EM, just type the new name....

The Holy Book says ...

sp_rename

Changes the name of a user-created object (for example, table, column, or user-defineddata type) in the current database.
Syntax

sp_rename [ @.objname = ] 'object_name' ,
[ @.newname = ] 'new_name'
[ , [ @.objtype = ] 'object_type' ]
Arguments

[@.objname =] 'object_name'
Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. object_name is nvarchar(776), with no default.
[@.newname =] 'new_name'
Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.
[@.objtype =] 'object_type'
Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.
ValueDescriptionCOLUMNA column to be renamed.DATABASEA user-defined database. This option is required when renaming a database.INDEXA user-defined index.OBJECTAn item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules.USERDATATYPEA user-defined data type added by executing sp_addtype.


I don't know why you are providing a round about way of doing the same thing.|||You can try out this one.

EXECUTE sp_rename N'MyTable.MyCurrFName', N'MyNewFName', 'COLUMN'

MyTable has a Field MyCurrFName. It will be renamed to MyNewFName|||You are better off either a). Create a view that has the name you want, or b). unloading, dropping, create, and load

There was a very good article that discusses why do the alter causes sql server to waste a lot of space on the data pages...Now if I can find on Nigel's web site I'll post a link|||Here's the link

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html|||thanks Brett, thats really a good link. never thought of it.

No comments:

Post a Comment