Dec 21, 2014

Add,drop,alter and rename Column using sql query

 
Following queries may help you to find, update, drop or add column from a table.

CREATE TABLE TMP(id INT IDENTITY(1,1),fName VARCHAR(20))
SELECT * FROM TMP
ALTER TABLE TMP ADD lName VARCHAR(50) NULL--Add column to exist table
SELECT * FROM TMP
EXEC [sys].[sp_rename] '[dbo].[TMP].[lName]','LastName','COLUMN' -- Query for rename column, here lName renamed to Last Name
SELECT * FROM TMP
ALTER TABLE TMP ALTER COLUMN fName NVARCHAR(200) -- Query for alter column, here I have changed the column fName datatype from VARCHAR(50) to NVARCHAR(200)
SELECT COLUMN_NAME AS [Column Name],DATA_TYPE AS [Data Type],
CHARACTER_MAXIMUM_LENGTH AS [Character Max Lenth],IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='TMP' AND COLUMN_NAME='fName'
Above query use to find a particular column exist in table or not..
ALTER TABLE TMP DROP COLUMN fName --Query for drop column
SELECT * FROM TMP
DROP TABLE TMP