sql判断表、字段是否存在和增加字段示例

if not exists (select * from dbo.sysobjects where id = object_id(N'Print_BillFormat') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  /*打印_用户格式*/
 CREATE table Print_BillFormat
 (BillID varchar(50) not null,
  PrintFormat text null,
  FormatID   varchar(100) not null,
  OrgID   varchar(50)
 )
END

/*打印_预制格式*/
if not exists (select * from dbo.sysobjects where id = object_id(N'Print_Bill') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
 CREATE table Print_Bill
 (BillMKID    varchar(4) not null,  /*单据所属模块ID*/
  BillID      varchar(50)  primary key not null, /*单据逻辑名称*/
  BillName    varchar(50)  not null,  /*单据显示名称*/
  RecordsPath varchar(200) null ,
  ParamCount  int default 0 /*生成的XML中的参数个数*/
 )
END
ELSE
BEGIN
 /*===============================*/
 /*修改Print_Bill的表结构*/
 /*===============================*/
    if not exists(select * from dbo.syscolumns where name='BillMKID' and id=object_id('Print_Bill'))
    BEGIN
    alter table Print_bill add BillMKID   varchar(4) default '' not null
    END
 
 exec sp_rename 'print_bill','print_billtemp'
 
 CREATE table Print_Bill
 (
  BillMKID    varchar(4) not null,  /*单据所属模块ID*/
  BillID      varchar(50)  primary key not null, /*单据逻辑名称*/
  BillName    varchar(50)  not null,  /*单据显示名称*/
  RecordsPath varchar(200) null ,
  ParamCount  int default 0, /*生成的XML中的参数个数*/
 )
 
 insert into Print_Bill(BillMKID,BillID,BillName,RecordsPath,ParamCount)
 select BillMKID,BillID,BillName,RecordsPath,ParamCount
 from print_billtemp
 
 drop table print_billtemp
END