T-SQL Alter Statements in SQL Server

This article describes how to alter and modify objects in a database.
The ALTER statement is part of DDL(Data Definition Language) statements.
Uses ALTER statement to modify objects like: database, table, view, trigger, procedure, index, role.

ALTER DATABASE

The T-SQL statement ALTER DATABASE is used to alter a SQL Server database.


				ALTER DATABASE database_name;

ALTER TABLE

The T-SQL statement ALTER TABLE is used to modify a table in the current database.


				ALTER TABLE table_name ADD column_name data_type;
				
ALTER TABLE table_name ALTER COLUMN column_name data_type;
				
ALTER TABLE table_name DROP COLUMN column_name;
				

ALTER TABLE test_table 
				
ADD CONSTRAINT constraint_name DEFAULT '' FOR column_name;

ALTER VIEW

To modify a view in SQL Server database uses ALTER VIEW statement.


				ALTER VIEW view_name
				
AS
				
SQL statement

ALTER TRIGGER

To modify a trigger is used the command ALTER TRIGGER.

			
				ALTER TRIGGER trigger_name
				
ON { table_name | view_name }
				
{ FOR | AFTER | INSTEAD OF }
				
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
				
AS { sql_statement [ ; ] }

ALTER PROCEDURE

The T-SQL statement ALTER PROCEDURE is used to modify a procedure.


				ALTER PROCEDURE procedure_name
				
AS
				
	SQL statement
				
GO;

ALTER INDEX

The T-SQL statement ALTER INDEX is used to modify a index table.


				ALTER INDEX index_name ON table_name REBUILD;
				
ALTER INDEX ALL ON table_name REBUILD; 	
				
ALTER INDEX index_name ON table_name.column_name DISABLE;	
				
ALTER INDEX test_index on test_table PAUSE;	
				
ALTER INDEX test_index on test_table ABORT;

ALTER ROLE

The T-SQL statement ALTER ROLE is used to add or drop a role in database.


				ALTER ROLE role_name ADD MEMBER user_name;  
				
ALTER ROLE role_name DROP MEMBER user_name;

T-SQL Tutorial