Tuesday, May 5, 2009

How to changes column size and column name through t-sql

Alter a column size in a table:

Syntax: Alter Table TableName Alter Column ColumnName DataType(Size)

Example: Alter Table dbo.[AddressType] Alter Column [Address] nvarchar(512);

Change ColumnName, DataType in a Table:
In t-sql, if we want to change the column name and data type as well, then it's some what complex than any other alter statements, below explain the process of how to change the column name as well as the data type change.

Syntax:
--Changing the column name.
Alter Table TableName Alter Column ColumnName DataType NULL/NOT NULL

EXEC sp_rename //Stored Procedure.
@objname = ' TableName. OldColumnName’,
@newname = 'New ColumnName',
@objtype = 'COLUMN'

Example:
Alter Table AddressType Alter Column Address VARCHAR(512) NULL

EXEC sp_rename
@objname = 'AddressType.Address',
@newname = 'Address1',
@objtype = 'COLUMN'

How good it is? Did you find any best way other than this?

No comments:

Post a Comment