lyfz_repos/经典版源码/document/数据库新版本脚本/UpdateLogDB.sql

/*---------------------------创建 logdb----------------------------------*/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'?Catalog?')
BEGIN
	--判断文件是否存在
	declare	@retval int, @ret int
	execute @retval=master.dbo.xp_fileexist N'?Catalogpath?\?Catalog?.mdf', @ret output
	if @ret=0
	begin
		--不存在,就创建
		PRINT '数据库[?Catalog?]不存在,启动创建过程'
		CREATE DATABASE [?Catalog?] ON PRIMARY 
		(
			NAME = N'?Catalog?', 
			FILENAME = N'?Catalogpath?\?Catalog?.mdf',
			SIZE = 5MB , 
			MAXSIZE = UNLIMITED, 
			FILEGROWTH = 1024KB 
		)
		LOG ON 
		( 
			NAME = N'?Catalog?_log', 
			FILENAME = N'?Catalogpath?\?Catalog?_log.ldf',
			SIZE = 3MB , 
			MAXSIZE = 10GB , 
			FILEGROWTH = 10%
		)
	end
	else
	begin
		--存在,附加到数据库里
		EXEC sp_attach_db
		@dbname = N'?Catalog?',
		@filename1 = N'?Catalogpath?\?Catalog?.mdf',
		@filename2 = N'?Catalogpath?\?Catalog?_log.ldf'
	end
END
ELSE
	PRINT '数据库[?Catalog?]已存在'
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'?Catalog?')
BEGIN
	EXEC dbo.sp_dbcmptlevel @dbname=N'?Catalog?', @new_cmptlevel=90

	IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
	begin
		EXEC [?Catalog?].[dbo].[sp_fulltext_database] @action = 'enable'
	end
END


use [?Catalog?]
declare @tablename varchar(16)
SELECT @tablename=DATEPART(year, GETDATE())
print @tablename

--判断表是否存在
if not exists(select * from dbo.sysobjects where id = object_id(N'@tablename') AND type in (N'U'))
begin
	print '11111'
	CREATE TABLE [dbo].[@tablename](
		[LogID] [int] IDENTITY(1,1) NOT NULL,						--日志ID
		[Account] [varchar](50) NOT NULL,							--帐户
		[UserName] [nvarchar](24) NULL,								--用户名
		[LogDate] [varchar](50) NOT NULL,							--记录时期
		[OptType] [nvarchar](50) NOT NULL,							--操作类型
		[SNNo] [varchar](1024) NULL,								--S/N硬件编号加密后的信息
		[LogDesc] [nvarchar](max) NOT NULL,							--记录内容
		 CONSTRAINT [PK_@tablename] PRIMARY KEY CLUSTERED 
		(
			[LogID] 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
else
begin
	print '22222'
	IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id(N'@tablename') AND name='LogID')	-- 判断字段是否存在;
		ALTER TABLE [dbo].[@tablename] ADD [LogID] [int] IDENTITY(1,1) NOT NULL
	IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id(N'@tablename') AND name='Account')	-- 判断字段是否存在;
		ALTER TABLE [dbo].[@tablename] ADD [Account] [varchar](50) NOT NULL
	IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id(N'@tablename') AND name='UserName')	-- 判断字段是否存在;
		ALTER TABLE [dbo].[@tablename] ADD [UserName] [nvarchar](24) NULL
	IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id(N'@tablename') AND name='LogDate')	-- 判断字段是否存在;
		ALTER TABLE [dbo].[@tablename] ADD [LogDate] [varchar](50) NOT NULL
	IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id(N'@tablename') AND name='OptType')	-- 判断字段是否存在;
		ALTER TABLE [dbo].[@tablename] ADD [OptType] [nvarchar](50) NOT NULL
	IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id(N'@tablename') AND name='SNNo')	-- 判断字段是否存在;
		ALTER TABLE [dbo].[@tablename] ADD [SNNo] [varchar](1024) NULL
	IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id(N'@tablename') AND name='LogDesc')	-- 判断字段是否存在;
		ALTER TABLE [dbo].[@tablename] ADD [LogDesc] [nvarchar](max) NOT NULL
end