1.判断和删除约束(Constrains)
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[FK_table1_table2]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1
)
ALTER TABLE dbo.table1 DROP CONSTRAINT FK_table1_table2
if (object_id('FK_StockOut_SALESORDER') is not null)
begin
alter table Stockout drop constraint FK_StockOut_SALESORDER
end
1.1 添加外键约束:
if(object_id('FK_StockOut_salesorderitem') is null)
begin
ALTER TABLE [dbo].[StockOut]
WITH CHECK ADD CONSTRAINT [FK_StockOut_salesorderitem]
FOREIGN KEY(salesorderitemid) REFERENCES [dbo].[salesorderitem] ([id])
end
2.判断和删除列(drop column)
if exists(select 1 from syscolumns
where id = object_id('StockOut') and name = 'Salesorderid')
begin
Alter table StockOut
drop column Salesorderid
end
3.添加列(add column)
if exists(select 1 from syscolumns
where id = object_id('StockOut') and name = 'salesorderitemid')
begin
print 'colomn salesorderitemid already exists'
end
else
begin
alter table StockOut
add salesorderitemid int null
end
4. 重命名列(Remane column):
if exists(select 1 from syscolumns
where id = object_id('Inventory') and name = 'FreeNumber')
begin
EXEC sp_rename 'Inventory.FreeNumber', 'FreeQuantity', 'COLUMN';
end
else
begin
print 'colomn FreeQuantity already exists'
end
GO
5.