sql语句创建数据库

说明

简单使用：拷贝下列代码，替换数据库路径及数据库名称即可使用。
当然你可以根据需要更改代码。
注意：
1.主文件，日志需要有，文件组可以根据需要修改。
2.登录用户、用户信息根据需要设置成你自己的用户名称和登录名称。
3.默认值设置为-1、0、1，你可以根据需要修改。

代码

--1.开启xp_cmdshell
EXECUTE sp_configure 'show advanced options', 1; --启用xp_cmdshell的高级配置
GO
RECONFIGURE;
GO
EXECUTE sp_configure 'xp_cmdshell', 1;--打开xp_cmdshell,可以调用SQL系统之外的命令
GO
RECONFIGURE;
GO
--2.如果不存在文件夹，创建文件夹
CREATE TABLE #T_FolderExists (FileExists BIT, IsDirectory BIT, ParentDirectoryExists BIT);
INSERT INTO #T_FolderExists
EXECUTE MASTER.dbo.xp_fileexist 'D:\Database\JERP\';
IF NOT EXISTS
    (
        SELECT
            *
        FROM
            #T_FolderExists
        WHERE
            IsDirectory = 1
            AND ParentDirectoryExists = 1
    )
BEGIN
    EXECUTE master.dbo.xp_cmdshell 'MKDIR D:\Database\JERP\';
END;
DROP TABLE #T_FolderExists;
GO
--创建临时表，是否存在数据库
CREATE TABLE #T_Temp (IsNewDatabase BIT NOT NULL DEFAULT (0));
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'JERP')
BEGIN
    INSERT INTO #T_Temp VALUES (0);
END;
ELSE
BEGIN
    INSERT INTO #T_Temp VALUES (1);
END;
GO
--创建数据库，主文件，文件组，日志
USE MASTER;
GO
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'JERP')
BEGIN
    CREATE DATABASE JERP --数据库名称
        ON PRIMARY
               (
                   NAME = N'JERP',--主文件名称
                   FILENAME = N'D:\Database\JERP\JERP.MDF',--主文件路径
                   SIZE = 10240KB,--主文件大小
                   MAXSIZE = UNLIMITED,--最大值
                   FILEGROWTH = 10%--增长率
               ),
        FILEGROUP BaseData --文件组
            (
                NAME = N'BaseData',
                FILENAME = N'D:\Database\JERP\JERP_Base_Data.NDF',
                SIZE = 10240KB,
                MAXSIZE = UNLIMITED,
                FILEGROWTH = 10%
            ),
        FILEGROUP BaseIndex
            (
                NAME = N'BaseIndex',
                FILENAME = N'D:\Database\JERP\JERP_Base_Index.NDF',
                SIZE = 10240KB,
                MAXSIZE = UNLIMITED,
                FILEGROWTH = 10%
            ),
        FILEGROUP SaleData
            (
                NAME = N'SaleData',
                FILENAME = N'D:\Database\JERP\JERP_Sale_Data.NDF',
                SIZE = 10240KB,
                MAXSIZE = UNLIMITED,
                FILEGROWTH = 10%
            ),
        FILEGROUP SaleIndex
            (
                NAME = N'SaleIndex',
                FILENAME = N'D:\Database\JERP\JERP_Sale_Index.NDF',
                SIZE = 10240KB,
                MAXSIZE = UNLIMITED,
                FILEGROWTH = 10%
            ),
        FILEGROUP PurchaseData
            (
                NAME = N'PurchaseData',
                FILENAME = N'D:\Database\JERP\JERP_Purchase_Data.NDF',
                SIZE = 10240KB,
                MAXSIZE = UNLIMITED,
                FILEGROWTH = 10%
            ),
        FILEGROUP PurchaseIndex
            (
                NAME = N'PurchaseIndex',
                FILENAME = N'D:\Database\JERP\JERP_Purchase_Index.NDF',
                SIZE = 10240KB,
                MAXSIZE = UNLIMITED,
                FILEGROWTH = 10%
            ),
        FILEGROUP ProduceData
            (
                NAME = N'ProduceData',
                FILENAME = N'D:\Database\JERP\JERP_Produce_Data.NDF',
                SIZE = 10240KB,
                MAXSIZE = UNLIMITED,
                FILEGROWTH = 10%
            ),
        FILEGROUP ProduceIndex
            (
                NAME = N'ProduceIndex',
                FILENAME = N'D:\Database\JERP\JERP_Produce_Index.NDF',
                SIZE = 10240KB,
                MAXSIZE = UNLIMITED,
                FILEGROWTH = 10%
            )
        LOG ON --日志
            (
                NAME = N'JERP_Log',
                FILENAME = N'D:\Database\JERP\JERP.LDF',
                SIZE = 10240KB,
                MAXSIZE = UNLIMITED,
                FILEGROWTH = 10%
            );
END;
GO
--数据库配置
IF EXISTS (SELECT * FROM #T_Temp WHERE IsNewDatabase = 1)
BEGIN
    ALTER DATABASE JERP SET COMPATIBILITY_LEVEL = 100;--兼容性级别。100 代表 SQL Server 2008

    IF (1 = FULLTEXTSERVICEPROPERTY ('IsFullTextInstalled'))
    BEGIN
        EXECUTE JERP.dbo.sp_fulltext_database @action = 'ENABLE'; --数据库启用全文索引
    END;

    ALTER DATABASE JERP SET ANSI_NULL_DEFAULT OFF;

    ALTER DATABASE JERP SET ANSI_NULLS OFF;

    ALTER DATABASE JERP SET ANSI_PADDING OFF;

    ALTER DATABASE JERP SET ANSI_WARNINGS OFF;

    ALTER DATABASE JERP SET ARITHABORT OFF;

    ALTER DATABASE JERP SET AUTO_CLOSE OFF;

    ALTER DATABASE JERP SET AUTO_SHRINK OFF;

    ALTER DATABASE JERP SET AUTO_UPDATE_STATISTICS ON;

    ALTER DATABASE JERP SET CURSOR_CLOSE_ON_COMMIT OFF;

    ALTER DATABASE JERP SET CURSOR_DEFAULT GLOBAL;

    ALTER DATABASE JERP SET CONCAT_NULL_YIELDS_NULL OFF;

    ALTER DATABASE JERP SET NUMERIC_ROUNDABORT OFF;

    ALTER DATABASE JERP SET QUOTED_IDENTIFIER OFF;

    ALTER DATABASE JERP SET RECURSIVE_TRIGGERS OFF;

    ALTER DATABASE JERP SET DISABLE_BROKER;

    ALTER DATABASE JERP SET AUTO_UPDATE_STATISTICS_ASYNC OFF;

    ALTER DATABASE JERP SET DATE_CORRELATION_OPTIMIZATION OFF;

    ALTER DATABASE JERP SET TRUSTWORTHY OFF;

    ALTER DATABASE JERP SET ALLOW_SNAPSHOT_ISOLATION OFF;

    ALTER DATABASE JERP SET PARAMETERIZATION SIMPLE;

    ALTER DATABASE JERP SET READ_COMMITTED_SNAPSHOT OFF;

    ALTER DATABASE JERP SET HONOR_BROKER_PRIORITY OFF;

    ALTER DATABASE JERP SET READ_WRITE;

    ALTER DATABASE JERP SET RECOVERY SIMPLE;

    ALTER DATABASE JERP SET MULTI_USER;

    ALTER DATABASE JERP SET PAGE_VERIFY CHECKSUM;

    ALTER DATABASE JERP SET DB_CHAINING OFF;
END;
GO

DROP TABLE #T_Temp;
GO
--描述信息
IF NOT EXISTS (SELECT * FROM JERP.sys.extended_properties WHERE name = 'MS_Description')
BEGIN
    EXECUTE JERP.sys.sp_addextendedproperty
        @name = N'MS_Description',
        @value = 'JERP';
END;
GO

USE JERP;
GO
--判断T_User是否存在，存在删除
IF EXISTS
    (
        SELECT
            *
        FROM
            sys.objects --在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）
        WHERE
            object_id = OBJECT_ID ('dbo.T_User')
            AND OBJECTPROPERTY (object_id, 'isTable') = 1
    )
    DROP TABLE dbo.T_User;
GO
--添加登录用户信息
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = 'workuser')
BEGIN
    EXECUTE sp_addlogin
        @loginame = 'workuser',
        @passwd = 'workuser123',
        @defdb = 'JERP';
END;
GO
--添加用户
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = 'workuser')
BEGIN
    EXECUTE sp_adduser @loginame = 'workuser', @name_in_db = 'workuser';
END;
GO
--添加默认值
IF EXISTS
    (
        SELECT
            *
        FROM
            sys.objects
        WHERE
            object_id = OBJECT_ID ('dbo.DF_Number0')
            AND OBJECTPROPERTY (object_id, 'isDefault') = 1
    )
    DROP DEFAULT dbo.DF_Number0;
GO

CREATE DEFAULT dbo.DF_Number0 AS 0;
GO

IF EXISTS
    (
        SELECT
            *
        FROM
            sys.objects
        WHERE
            object_id = OBJECT_ID ('dbo.DF_Number1')
            AND OBJECTPROPERTY (object_id, 'isDefault') = 1
    )
    DROP DEFAULT dbo.DF_Number1;
GO

CREATE DEFAULT dbo.DF_Number1 AS 1;
GO

IF EXISTS
    (
        SELECT
            *
        FROM
            sys.objects
        WHERE
            object_id = OBJECT_ID ('dbo.DF_ParentID')
            AND OBJECTPROPERTY (object_id, 'isDefault') = 1
    )
    DROP DEFAULT dbo.DF_ParentID;
GO

CREATE DEFAULT dbo.DF_ParentID AS-1;
GO