SQL_DDL_建库建表

efdf

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