SETHA SITE INFORMATION

  • Increase font size
  • Default font size
  • Decrease font size
SQL

How to change data type of field in table using T-SQL?

E-mail Print PDF
Using below T-SQL script to change data type of the table's field in Microsoft SQL Server.
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type;
 

How to rename column in SQL Server using T-SQL?

E-mail Print PDF
Suppose we have database named StudentDB and table named Student in Microsoft SQL Server 2008.
Below T-SQL code uses to rename column StdID of table Student to new column named StudentID.
USE StudentDB

EXEC sp_rename 'Student.StdID', 'StudentID','COLUMN'
 

How to Add Foreign Key, Delete and Update CASCADE to Existing Table in T-SQL?

E-mail Print PDF
Microsoft SQL Server uses T-SQL (Transact SQL) to access database objects and manipulate data in the Database. If we create one Table and forgot to put a foreign key constraint including CASCADE options on a field that is a foreign key of other Table, we can use the following CODE.
ALTER TABLE table_name
WITH NOCHECK ADD CONSTRAINT fk_constraint_name
	FOREIGN KEY(foreign_key_field)
	REFERENCES reference_table(primary_key_field)
	[ON DELETE CASCADE]
	[ON UPDATE CASCADE]

For example, we have existing Tables as below:
student(id, stname, gender)
studentphone(id, phone_number)
 
Table studentphone is a child table of student but we forgot to put a foreign key constraint on field [id] in studentphone. To add a foreign key constraint on field [id] with CASCADE options in Table studentphone please see the code below.
ALTER TABLE studentphone
WITH NOCHECK ADD CONSTRAINT fk_student_phone
	FOREIGN KEY(id)
	REFERENCES studenet(id)
	ON DELETE CASCADE
	ON UPDATE CASCADE