Change Table name
EXEC sp_rename 'OriginalName', 'NewName'
Change Column name
sys.sp_rename 'TableName.ColumnName', 'NewColumnName'
Add Foreign Key
ALTER TABLE dbo.[TableName] ADD CONSTRAINT FK_Table1Name_Table2Name FOREIGN KEY (Table1ForeignColumnName) REFERENCES dbo.[Table2Name](ID) ON UPDATE NO ACTION ON DELETE NO ACTION GO
Note: The name of the constraint “FK_Table1_Table2” should have the name of the table with the foreign key first, and the table with the main id second as a standard.
Removing Foreign Key Constraint
As these constraints normally have a standarized name we should be able to do this:
ALTER TABLE tablename DROP CONSTRAINT ConstraintName
-- Normally ConstraintName is FK_Table1Name_Table2Name where Table1 references Table2 Primary Key
In case you want to get the constraint name programatically:
// TODO
SELECT KCU.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU WHERE KCU.TABLE_NAME = 'Message' AND KCU.COLUMN_NAME = 'MaintenanceID'
Add Default value to existing column
ALTER TABLE TableName ADD CONSTRAINT ConstraintName DEFAULT value FOR ColumnName
Note: As a convention, Constraint name here should be: DEF_ColumnName
Removing Default Constraint
declare @key nvarchar(200);
select @key = c.name from sys.all_columns a
inner join sys.tables b on a.object_id = b.object_id
inner join sys.default_constraints c on a.default_object_id = c.object_id
where b.name='tablename' and a.name = 'columnname'
exec('alter table tablename drop constraint ' + @key)
-- in case you also wanted to remove the column:
alter table tablename drop column columnname
