--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