Add,drop,alter and rename Column using sql query


Following queries may help you to find, update, drop or add a 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

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...