建库及建存储过程代码

use master
create database TestDB
Go
 
DECLARE @ver int
select @ver=
case
 when ver like '%2000%' then 2000-- insert into testdb(ver) values(2000)  
 when ver like '%2005%' then 2005
 when ver like '%2008%' then 2008
end
from (select @@VERSION as ver) as d
/*-------------------------------------------------------SQL SERVER 2008----------------------------------------------------------------*/
IF @ver =2008
 BEGIN
  EXEC sp_droplogin 'TestUser'
  create login TestUser with password='TestPWD', DEFAULT_DATABASE =TestDB,CHECK_EXPIRATION=off ,CHECK_POLICY = OFF
  EXEC sp_addsrvrolemember 'TestUser','sysadmin'
  EXEC sp_addsrvrolemember 'TestUser','serveradmin'
  EXEC sp_addsrvrolemember 'TestUser','securityadmin'
  EXEC sp_addsrvrolemember 'TestUser','dbcreator'  
  USE [TestDB]
  CREATE USER [TestUser] FOR LOGIN [TestUser]
  print 2008
 END
/*-------------------------------------------------------SQL SERVER 2008  End-----------------------------------------------------------*/

/*-------------------------------------------------------SQL SERVER 2000----------------------------------------------------------------*/
ELSE IF @ver =2000
 BEGIN
  EXEC sp_droplogin 'TestUser'
  EXEC sp_addlogin 'TestUser','TestPWD','TestDB'
  EXEC sp_addsrvrolemember 'TestUser','sysadmin'
  EXEC sp_addsrvrolemember 'TestUser','serveradmin'
  EXEC sp_addsrvrolemember 'TestUser','securityadmin'
  EXEC sp_addsrvrolemember 'TestUser','dbcreator'
  use [TestDB]
  EXEC sp_changedbowner 'TestUser'
  print 2000
 END
/*-------------------------------------------------------SQL SERVER 2000  End-----------------------------------------------------------*/
alter database TestDB set recovery SIMPLE

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[chkjcb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[chkjcb]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[chkjcrb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[chkjcb]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE chkjcb AS
/*-----------------常量表（共6个）-----------------------------------------------------------------------------------*/
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name='Flag')
    begin
CREATE TABLE [dbo].[Flag] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [kname] [smallint] NULL DEFAULT (0),
 [edzrs] [smallint] NULL DEFAULT (0),
 [jxzrs] [smallint] NULL DEFAULT (0),
 [jxbjrs] [smallint] NULL DEFAULT (0),
 [jrxjrs] [smallint] NULL DEFAULT (0),
 [Timer] [datetime] NULL ,
 [flag] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL DEFAULT (0),
 [IsExit] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL DEFAULT (0)
) ON [PRIMARY]
insert into flag(kname,timer,IsExit) values('0','2007-08-29 00:00:00','1')
    END
   
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

----------建立日表-----------------------------------------------
CREATE PROCEDURE chkjcrb @tbname nvarchar(8) AS
DECLARE @btablename nvarchar(12)
SET @btablename='JC_B'+@tbname
DECLARE @mtablename nvarchar(12)
SET @mtablename='JC_M'+@tbname
DECLARE @rtablename nvarchar(12)
SET @rtablename='JC_R'+@tbname
DECLARE @mctablename nvarchar(13)
SET @mctablename='JC_MC'+@tbname
DECLARE @logtablename nvarchar(11)
SET @logtablename='LOG'+@tbname
DECLARE @lltablename nvarchar(13)
SET @lltablename='JC_LL'+@tbname

IF NOT EXISTS (SELECT name FROM sysobjects WHERE name=@btablename)
    begin
    exec('CREATE TABLE ['+@btablename+'] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [fzh] [smallint] NULL ,
 [kh] [tinyint] NULL ,
 [devid] [tinyint] NULL ,
 [wzid] [smallint] NULL ,
 [point] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
 [type] [tinyint] NULL ,
 [stime] [datetime] NULL ,
 [etime] [datetime] NULL ,
 [ssz] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [zdz] [real] NULL ,
 [pjz] [real] NULL ,
 [zdzs] [datetime] NULL ,
 [cs] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
 [kzk] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
 [upflag] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL DEFAULT (0)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]')
    end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--执行存储过程建表----------------------------------
EXEC chkjcb

DECLARE @tablename NVARCHAR(10)
SET @tablename = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + right('00'+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2) + right('00'+CAST(DAY(GETDATE()) AS VARCHAR(2)),2)
EXEC('chkjcrb ' + @tablename)