创建数据库:
USE MASTER
GO
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='pro_CreateDataBases')
drop proc pro_CreateDataBases
GO
CREATE PROC pro_CreateDataBases
@DataBasesName varchar(50),
@FilePath varchar(255),
@rs int output
AS
begin
SET @rs=0
if exists(select * from sysdatabases where name=@DataBasesName)
execute(N'DROP DATABASE '+@DataBasesName)--删除数据库
execute(N'create database '+@DataBasesName+N'
on primary
(
NAME=N'''+@DataBasesName+'_DATA'',
FILENAME=N'''+@FilePath+N'\'+@DataBasesName+'_DATA.mdf'',
SIZE=5MB,
MAXSIZE=5000000MB
)
LOG ON
(
NAME=N'''+@DataBasesName+N'_LOG'',
FILENAME=N'''+@FilePath+N'\'+@DataBasesName+'_LOG.mdf'',
SIZE=5MB,
FILEGROWTH=0
)
')
if @@ERROR>0
begin
set @rs=@@ERROR
return;
end
end
GO
创建数据表
USE E_TinyCam_Monitor
GO
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='pro_CreateOrderInfoTable')
DROP PROC pro_CreateOrderInfoTable
GO
CREATE PROC pro_CreateOrderInfoTable
@TableName varchar(50),
@rs int output
AS
begin
set @rs=0
if exists(select * from sys.objects where name=@TableName)
execute (N'drop table '+@TableName)
execute(N'create table '+@TableName+N'
([Id] int identity(1,1)not null,
[UID] VARCHAR(50) NOT NULL,
[CMEI] VARCHAR(50)NOT NULL,
[KEY] VARCHAR(255)NOT NULL,
[SN] VARCHAR(50)NOT NULL,
[MAC] VARCHAR(50),
[Box] VARCHAR(MAX),
[test1] VARCHAR(MAX),
[test2] VARCHAR(MAX),
[test3] VARCHAR(MAX),
[test4] VARCHAR(MAX),
[test5] VARCHAR(MAX),
[test6] VARCHAR(MAX),
[DATE] VARCHAR(50)
)'
)
if @@ERROR>0
begin
set @rs=@@ERROR
return;
end
---execute(N'
--- ALTER TABLE '+@TableName+N' ADD CONSTRAINT UNIQUE_UID UNIQUE(UID)
--- ALTER TABLE '+@TableName+N' ADD CONSTRAINT UNIQUE_CMEI UNIQUE(CMEI)
--- ALTER TABLE '+@TableName+N' ADD CONSTRAINT UNIQUE_KEY UNIQUE([KEY])
--- ALTER TABLE '+@TableName+N' ADD CONSTRAINT UNIQUE_SN UNIQUE(SN)
--- ALTER TABLE '+@TableName+N' ADD CONSTRAINT UNIQUE_MAC UNIQUE(MAC)')
---if @@ERROR>0
---begin
--- set @rs=@@ERROR
--- return;
---end
end
GO