--增加关联(添加表TableA与表TableB的关系)
if not exists (select * from sysobjects where id = object_id('FK_TableA_TableB'))
begin
ALTER TABLE [dbo].[TableA] ADD
CONSTRAINT [FK_TableA_TableB] FOREIGN KEY
(
[TableB_TableBID] --TableA中的外建
) REFERENCES [dbo].[TableB] (
[TableBID] --TableB的主键
)
end
GO
--删除关联
if exists (select * from sysobjects where id = object_id('FK_TableA_TableB'))
begin
ALTER TABLE [dbo].[TableA] drop CONSTRAINT [FK_TableA_TableB]
end
GO
--增加唯一约束
if not exists (select * from sysobjects where id = object_id('IX_TableA'))
begin
ALTER TABLE [dbo].[TableA] WITH NOCHECK ADD CONSTRAINT [IX_TableA] UNIQUE NONCLUSTERED
(
[TableAID] --TableA的主键
) ON [primary]
end
GO
--增加字段(在TableA中添加名为ColumnsA的字段)
IF (NOT EXISTS(Select * from syscolumns Where ([id] = (Select [id] From sysobjects Where [name] ='TableA') AND [name]= 'ColumnsA')))
Begin
Alter Table TableA Add ColumnsA char(20) NULL
End
GO
--删除字段
IF ( EXISTS(Select * From syscolumns Where ([id] = (Select [id] From sysobjects Where [name] ='TableA') AND [name] ='ColumnsA')))
Begin
Alter Table TableA drop column ColumnsA
End
GO
--修改字段长度
IF (EXISTS(Select * from syscolumns Where ([id] = (Select [id] From sysobjects Where [name] ='TableA') AND [name]= 'ColumnsA')))
Begin
Alter Table TableA Alter column ColumnsA char(50) NULL
End
GO