SQL_DDL_建库建表

--IF DB_ID('testdb') IS NULL
--CREATE DATABASE testdb
USE master
GO
IF EXISTS ( SELECT  *
            FROM    sys.databases
            WHERE   name = 'TestDB' ) --是否存在，存在删除
    DROP DATABASE TestDB
--GO
-- EXEC xp_cmdshell 'mkdir D:\SQL' --调用DOS命令创建文件夹
CREATE DATABASE TestDB ON PRIMARY --PRIMARY可选，用于创建主数据库文件
(
    NAME='TestDB1_data',--主数据库文件逻辑名
    FILENAME='D:\SQL\TestDB1_data.mdf',--主数据库文件物理名
    SIZE=5MB,--主数据库文件初始大小
    MAXSIZE=10MB,--主数据库文件最大大小
    FILEGROWTH = 10%--主数据库文件增长值
),
(
    NAME='TestDB2_data',--次数据库文件逻辑名
    FILENAME='D:\SQL\TestDB2_data.ndf',
    SIZE=5MB,
    MAXSIZE=10MB,
    FILEGROWTH = 10%
) LOG ON
(
    NAME='TestDB1_log',
    FILENAME='D:\SQL\TestDB1_log.1df',
    SIZE=5MB,
    MAXSIZE=10MB,
    FILEGROWTH = 10%
),
(
    NAME='TestDB2_log',
    FILENAME='D:\SQL\TestDB2_log.1df',
    SIZE=5MB,
    MAXSIZE=10MB,
    FILEGROWTH = 10%
)
GO
USE TestDB
GO
IF EXISTS ( SELECT  * FROM    sys.objects WHERE   name = 'TestTable1' AND type = 'u' ) 
    DROP TABLE TestTable1
CREATE TABLE TestTable1
    (
      [id] INT NOT NULL PRIMARY KEY IDENTITY(1, 1) ,
      [T1_id] INT NOT NULL UNIQUE,
      [name] VARCHAR(200) NOT NULL ,
      [age] INT NOT    NULL CHECK(age > 0),
      [remark] NVARCHAR(2000) NULL DEFAULT ('') 
    )

go
IF EXISTS ( SELECT  * FROM    sys.objects WHERE   name = 'TestTable2' AND type = 'u' ) 
    DROP TABLE TestTable2
CREATE TABLE TestTable2
    (
      [id] INT NOT NULL ,
      [T2_id] INT NOT NULL FOREIGN KEY REFERENCES TestTable1(id) ,
      [name] VARCHAR(200) NOT NULL ,
      [age] INT NOT    NULL ,
      [remark] NVARCHAR(2000) NULL       
    )
--在外部添加约束
ALTER TABLE dbo.TestTable2 ADD CONSTRAINT Pk_Id PRIMARY KEY (id) --主键
--ALTER TABLE dbo.TestTable2 DROP CONSTRAINT pk_id --删除主键
ALTER TABLE dbo.TestTable2 ADD CONSTRAINT Uq_Id UNIQUE(T2_id)--唯一
--ALTER TABLE dbo.TestTable2 DROP CONSTRAINT uq_id --删除唯一
ALTER TABLE dbo.TestTable2 ADD CONSTRAINT Ck_Age CHECK(age > 0 )--检查
--
ALTER TABLE    dbo.TestTable2 ADD CONSTRAINT Df_name DEFAULT('') FOR name
--ALTER TABLE dbo.TestTable2 DROP CONSTRAINT Df_name
ALTER TABLE dbo.TestTable2 ADD CONSTRAINT Fk_Id FOREIGN KEY (T2_id) REFERENCES dbo.TestTable1 (id)
--
--USE master
--DROP DATABASE TestDB

快速建库

 

-- 1.搜索 [Sharp4DemoDase] 更改为你要创建的库名称 

-- 2.搜索 N'Sharp4DBTest_log' 替换为新库的日志名称 

-- 3.搜索 N'Sharp4DBTest' 替换为新的库文件名 

-- 4.搜索 N'D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\Sharp4DBTest.mdf' 为新的库文件保存路径 ' 

-- 5.搜索 N'D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\Sharp4DBTest_log.ldf' 为新的日文件保存路途径 

-- 然后执行下面的脚本 

  

USE [master] 

GO 

/****** 对象 :  Database [Sharp4DemoDase]    脚本日期 : 12/28/2008 23:13:57 ******/ 

CREATE DATABASE [Sharp4DemoDase] ON PRIMARY 
( 
    NAME= N'Sharp4DBTest' ,
    FILENAME= N'D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\Sharp4DBTest.mdf',
    SIZE= 3072KB ,
    MAXSIZE= UNLIMITED,
    FILEGROWTH= 1024KB 
) LOG ON 
( 
    NAME= N'Sharp4DBTest_log' ,
    FILENAME= N'D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\Sharp4DBTest_log.ldf',
    SIZE= 1024KB ,
    MAXSIZE= 2048GB ,
    FILEGROWTH= 10%
) COLLATE Chinese_PRC_CI_AS 

GO 

EXEC dbo.sp_dbcmptlevel @dbname = N'Sharp4DemoDase', @new_cmptlevel = 90 
GO 
IF ( 1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') ) 
    BEGIN 
        EXEC [Sharp4DemoDase].[dbo].[sp_fulltext_database] @action = 'disable' 
    END 
GO 
ALTER DATABASE [Sharp4DemoDase] SET ANSI_NULL_DEFAULT OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET ANSI_NULLS OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET ANSI_PADDING OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET ANSI_WARNINGS OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET ARITHABORT OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET AUTO_CLOSE OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET AUTO_CREATE_STATISTICS ON 
GO 
ALTER DATABASE [Sharp4DemoDase] SET AUTO_SHRINK OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET AUTO_UPDATE_STATISTICS ON 
GO 
ALTER DATABASE [Sharp4DemoDase] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET CURSOR_DEFAULT  GLOBAL 
GO 
ALTER DATABASE [Sharp4DemoDase] SET CONCAT_NULL_YIELDS_NULL OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET NUMERIC_ROUNDABORT OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET QUOTED_IDENTIFIER OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET RECURSIVE_TRIGGERS OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET   ENABLE_BROKER 
GO 
ALTER DATABASE [Sharp4DemoDase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET TRUSTWORTHY OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO 
ALTER DATABASE [Sharp4DemoDase] SET PARAMETERIZATION SIMPLE 
GO 
ALTER DATABASE [Sharp4DemoDase] SET   READ_WRITE 
GO 
ALTER DATABASE [Sharp4DemoDase] SET RECOVERY FULL 
GO 
ALTER DATABASE [Sharp4DemoDase] SET   MULTI_USER 
GO 
ALTER DATABASE [Sharp4DemoDase] SET PAGE_VERIFY CHECKSUM   
GO 
ALTER DATABASE [Sharp4DemoDase] SET DB_CHAINING OFF 
  



USE [master] 
GO 
/****** 对象 :  Database [Test01]    脚本日期 : 12/29/2008 10:08:46 ******/ 
IF EXISTS ( SELECT  name
            FROM    sys.databases
            WHERE   name = N'Test01' ) 
    DROP DATABASE [Test01] 

 

参考：--更多超详细的约束请看 http://www.cnblogs.com/troywithblog/archive/2013/05/24/3096480.html