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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment