--麻醉图片记录表, 
--VEY1
/****** Object:  Table [dbo].[LTK1]    Script Date: 08/14/2017 09:44:59 ******/
IF  not  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VEY1]') AND type in (N'U'))
   create table VEY1(
    [VEY01] int primary key NONCLUSTERED --VEY01
  , [VAT01] int --手术记录表ID, 关联字段:VAT1.VAT01
  , [VEY03] int --图片序号
  , [VEY04] int --0为主图，1为分图
  , [VEY05] datetime --开始时间
  , [VEY06] datetime --结束时间
  , [VEY07] varchar(100) --图片地址URL
)
GO


 

/****** Object:  Table [dbo].[CCL1]    Script Date: 09/29/2017 18:05:44 ******/
IF  not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CCL1]') AND type in (N'U'))
 begin

CREATE TABLE [dbo].[CCL1](
	[CCL01] [int] NOT NULL,
	[BBX01] [int] NOT NULL,
	[BCK01] [int] NOT NULL,
	[CCL03] [int] NULL,
 CONSTRAINT [PK_CCL1_1] PRIMARY KEY CLUSTERED 
(
	[CCL01] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
end
GO

IF not   EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CCM1]') AND type in (N'U'))
begin 

CREATE TABLE [dbo].[CCM1](
	[CCM01] [int] NOT NULL,
	[BBX01] [int] NULL,
	[VAA05] [varchar](64) NULL,
	[CCM02] [varchar](256) NULL,
	[BCE01A] [int] NULL,
	[BCE03A] [varchar](64) NULL,
	[CCM05] [datetime] NULL,
	[CCM06] [datetime] NULL,
	[BCE01B] [int] NULL,
	[BCE03B] [int] NULL,
	[CCM09] [datetime] NULL,
	[BCE01C] [int] NULL,
	[BCE03C] [varchar](64) NULL,
	[CCM12] [datetime] NULL,
	[CCM13] [int] NULL,
	[CCM14] [int] NULL,
	[CCM15] [int] NULL,
	[VAA01] [int] NULL,
 CONSTRAINT [PK_CCM1] PRIMARY KEY CLUSTERED 
(
	[CCM01] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

end


GO



/****** Object:  Table [dbo].[CCJ1]    Script Date: 09/29/2017 18:08:09 ******/
IF  not  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CCJ1]') AND type in (N'U'))
begin

CREATE TABLE [dbo].[CCJ1](
	[CCJ01] [int] NOT NULL,
	[BCE01A] [int] NULL,
	[BCE01] [int] NULL,
	[BCE03] [varchar](64) NULL,
	[CCJ03] [datetime] NULL,
	[BCO01] [int] NULL
) ON [PRIMARY]

end




if not exists(select * from syscolumns where name = 'VDG16' and object_Name(id)='VDG1')
  alter table VDG1 add VDG16 varchar(30)
GO

if not exists(select * from syscolumns where name = 'VDG17' and object_Name(id)='VDG1')
  alter table VDG1 add VDG17 varchar(30)
GO

if not exists(select * from syscolumns where name = 'VDG18' and object_Name(id)='VDG1')
  alter table VDG1 add VDG18 tinyint   
GO

if not exists(select * from syscolumns where name = 'VDG19' and object_Name(id)='VDG1')
  alter table VDG1 add VDG19 varchar(200)  
GO



if not exists(select * from syscolumns where name = 'VAT54' and object_Name(id)='VAT1')
  alter table VAT1 add VAT54  varchar(400)   
GO

if not exists(select * from syscolumns where name = 'VAT56' and object_Name(id)='VAT1')
  alter table VAT1 add VAT56  varchar(1024)   
GO

if not exists(select * from syscolumns where name = 'Custom' and object_Name(id)='SYS_Scripts')
  alter table SYS_Scripts add Custom tinyint  
GO





--更改IDENTITY列为非IDENTITY列
 
if Exists(Select top 1 1 from sysobjects
Where objectproperty(id, 'TableHasIdentity') = 1
and upper(name) = upper('VDG1'))
begin  
 	
	if object_id('tempdb..##ISIDENTITY') is not null 
	begin
	   drop table  ##ISIDENTITY
	end  
   create TABLE ##ISIDENTITY( A int) 
end

GO
if object_id('tempdb..##ISIDENTITY') is not null 
begin
  exec('alter table VDG1 add   VDG01_bak int') 
end 

 GO
 
 if object_id('tempdb..##ISIDENTITY') is not null 
begin
  exec('update  VDG1 SET   VDG01_bak=vdg01') 
end 

 GO
 
if object_id('tempdb..##ISIDENTITY') is not null 
begin
	declare @pk varchar(100)
	 select @pk=name from sysobjects where parent_obj in (select id from sysobjects where name='VDG1')  and 
	xtype='pk'
	 if @pk<>'' 
	  exec(' ALTER TABLE VDG1  drop constraint '+ @pk)
end
 GO

 
if object_id('tempdb..##ISIDENTITY') is not null 
begin
  exec('alter table VDG1 drop column VDG01 ')
end  
GO
if object_id('tempdb..##ISIDENTITY') is not null 
begin
EXEC sp_rename 'VDG1.VDG01_bak', 'VDG01', 'COLUMN';
end
GO
if object_id('tempdb..##ISIDENTITY') is not null 
begin
 exec('alter table VDG1 alter column vdg01 int not null') 
end
GO

if object_id('tempdb..##ISIDENTITY') is not null 
begin
exec('alter table VDG1    add primary key (VDG01)')
end
GO

if object_id('tempdb..##ISIDENTITY') is not null 
begin
   drop table  ##ISIDENTITY
end  
 
GO

if object_id('tempdb..##ISIDENTITY') is not null 
begin
 declare @value int 
 declare @count int 
 select @count = MAX(vdg01)  from  VDG1
   EXEC Core_NewIDEx 'VDG1', 'VDG01', @value OUT,@count
end  
 GO






if not exists(select * from syscolumns where name = 'VAT57' and object_Name(id)='VAT1')
  alter table VAT1 add VAT57 numeric(9,2)  --身高
GO

if not exists(select * from syscolumns where name = 'VAT58' and object_Name(id)='VAT1')
  alter table VAT1 add VAT58 numeric(9,2)  --体重
GO