use tempdb

Create Table Employee
(
EmployeeId INT NOT NULL,
EmployeeName VARCHAR(25),
Salary INT,
ContactNo VARCHAR(10)
);

Create Table Department
(
DeptId INT PRIMARY KEY,
DeptName VARCHAR(50)
);

--You can verify changes to table structure using -> sp_help 'Employee'
 
--SQL Server - Add a Column to Existing Table
ALTER TABLE Employee
ADD  [Address] VARCHAR(255) NOT NULL
GO

--SQL Server - Drop an Existing column from a table
ALTER TABLE Employee
DROP COLUMN [Address]
GO

--SQL Server - Change data type of Existing column
ALTER TABLE Employee
ALTER COLUMN Salary DECIMAL(18,2)
GO

--SQL Server - Change Size of the Existing Column
ALTER TABLE Employee
ALTER COLUMN EmployeeName VARCHAR(50)
GO

--SQL Server -  Add A Primary Key Constraint to Existing Table
ALTER TABLE Employee
ADD   CONSTRAINT PK_Employee_EmployeeId PRIMARY KEY (EmployeeId) 
GO

--SQL Server -  Drop Existing Primary Key Constraint on a Existing Table
ALTER TABLE Employee
DROP  CONSTRAINT PK_Employee_EmployeeId
GO

--SQL Server - Add Foreign Key Constraint
ALTER TABLE Employee
ADD   CONSTRAINT Fk_Employee_DeptId  FOREIGN KEY (EmployeeId) REFERENCES Department(DeptId)
GO

--SQL Server -  Drop Foreign Key Constraint
ALTER TABLE Employee
DROP  CONSTRAINT Fk_Employee_DeptId
GO

--SQL Server -  Add Check Constraint to Existing Table
ALTER TABLE Employee
ADD   CONSTRAINT CK_Employee_Salary CHECK (Salary > 0)
GO

--SQL Server - Drop Check Constraint on Existing Table
ALTER TABLE Employee
DROP  CONSTRAINT CK_Employee_Salary
GO

--SQL Server - Add NULL or NOT NULL constraint to Existing Table
ALTER TABLE Employee
ALTER COLUMN ContactNo VARCHAR(10) NULL
GO

--SQL Server - Add Default Constraint to Existing Table
ALTER TABLE Employee
ADD   CONSTRAINT DF_Employee_Active
DEFAULT 0 FOR ACTIVE
GO

--SQL Server - Drop Default Constraint on Existing Table
ALTER TABLE Employee
DROP  CONSTRAINT DF_Employee_Active
GO

--SQL Server - Change Collation of an Existing Column
ALTER TABLE Employee 
ALTER COLUMN EmployeeName varchar(25) COLLATE Latin1_General_CI_AS NOT NULL;
GO

--SQL Server - Disable Foreign key Constraint
ALTER TABLE Employee
NOCHECK CONSTRAINT Fk_Employee_DeptId;
GO

--SQL Server - Disable Check Key Constraint
ALTER TABLE Employee
NOCHECK CONSTRAINT CK_Employee_Salary;
GO

--SQL Server- Rename a Column in a Table
EXEC sp_RENAME 'table_name.old_name', 'new_name', 'COLUMN'
GO