SQL SERVER存储过程动态创建数据库及数据表

漂泊_人生

于 2023-04-27 09:30:05 发布

阅读量743

点赞数

文章标签：数据库 sql sqlserver

本文链接：https://blog.csdn.net/u013934107/article/details/130399274

版权

创建数据库：

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