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
(
INT NOT NULL PRIMARY KEY IDENTITY(1, 1) ,
INT NOT NULL UNIQUE,
VARCHAR(200) NOT NULL ,
INT NOT NULL CHECK(age > 0),
NVARCHAR(2000) NULL DEFAULT ('')
)
go
IF EXISTS ( SELECT* FROM sys.objects WHERE name = 'TestTable2' AND type = 'u' )
DROP TABLE TestTable2
CREATE TABLE TestTable2
(
INT NOT NULL ,
INT NOT NULL FOREIGN KEY REFERENCES TestTable1(id) ,
VARCHAR(200) NOT NULL ,
INT NOT NULL ,
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.搜索 更改为你要创建的库名称
-- 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
GO
/****** 对象 :Database 脚本日期 : 12/28/2008 23:13:57 ******/
CREATE DATABASE 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 .. @action = 'disable' END GO ALTER DATABASE SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE SET ANSI_NULLS OFF GO ALTER DATABASE SET ANSI_PADDING OFF GO ALTER DATABASE SET ANSI_WARNINGS OFF GO ALTER DATABASE SET ARITHABORT OFF GO ALTER DATABASE SET AUTO_CLOSE OFF GO ALTER DATABASE SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE SET AUTO_SHRINK OFF GO ALTER DATABASE SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE SET CURSOR_DEFAULTGLOBAL GO ALTER DATABASE SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE SET ENABLE_BROKER GO ALTER DATABASE SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE SET TRUSTWORTHY OFF GO ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE SET PARAMETERIZATION SIMPLE GO ALTER DATABASE SET READ_WRITE GO ALTER DATABASE SET RECOVERY FULL GO ALTER DATABASE SET MULTI_USER GO ALTER DATABASE SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE SET DB_CHAINING OFF
USE GO /****** 对象 :Database 脚本日期 : 12/29/2008 10:08:46 ******/ IF EXISTS ( SELECTname FROM sys.databases WHERE name = N'Test01' ) DROP DATABASE
页:
[1]