34,575
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE createDB
-- Add the parameters for the stored procedure here
@DBname varchar(20) = 'dqleakdb_test'
--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- 如果已经存在需要创建的数据库,则先删除了,然后再重新创建
IF exists (SELECT * FROM sys.databases WHERE name = @DBname)
Exec ( 'drop database' + @DBname )
DECLARE @s varchar(2000)
set @s = '
CREATE DATABASE ' + @DBname +'
ON
(
NAME = ' + @DBname + '_Data,
FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' + @DBname + '.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5
)
LOG ON
(
NAME = ' + @DBname + '_Log,
FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' + @DBname + 'Log.mdf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
'
EXEC ( @s )
END
GO
CREATE PROCEDURE createTable
-- Add the parameters for the stored procedure here
@TableName varchar(20) = 'ZCXXB'
--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- 如果已经存在需要创建的表,则先删除了,然后再重新创建
IF exists (select * from sysobjects where id = object_id(@TableName))
-- IF exists (SELECT * FROM sys.databases WHERE name = @DBname)
Exec ( 'drop table' + @TableName )
IF @TableName = 'ZCXXB'
BEGIN
DECLARE @s varchar(2000)
set @s = '
CREATE TABLE ' + @TableName + '
GDQYID char(3) NOT NULL
GDBHID char(3) NOT NULL
SBID char(3) NOT NULL
IP varchar(15) NOT NULL
PORT INT NOT NULL
'
END
EXEC ( @s )
END
GO
EXEC createDB 'TEST'
USE TEST
EXEC createTable
EXEC createDB 'TEST'
USE TEST
go
EXEC createTable