数据库

USE master
IF EXISTS (
  SELECT * 
    FROM sys.databases 
   WHERE name = N'TESTONE'
)
  DROP DATABASE TESTONE
GO
CREATE DATABASE TESTONE ON  PRIMARY 
( 
NAME = N'TESTONE_MDF',
FILENAME = N'E:\Database\TESTONE.mdf' ,
SIZE = 5120KB ,
MAXSIZE = 10240KB ,
FILEGROWTH = 15%)
LOG ON 
( 
NAME = N'TESTONE_LOG', 
FILENAME = N'E:\Database\TESTONE.LOG' ,
SIZE = 1024KB , 
MAXSIZE = 10240KB ,
FILEGROWTH = 10%
)
GO
----------------------------------------------------------------
USE master
IF EXISTS (
  SELECT * 
    FROM sys.databases 
   WHERE name = N'TESTTWO'
)
  DROP DATABASE TESTTWO
GO
CREATE DATABASE TESTTWO
ON 
(
NAME='TESTTWO_MDF',
FILENAME='E:\DATABASE\TESTTWO.MDF',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=15%
),
(
NAME='TESTTWO_NDF',
FILENAME='E:\DATABASE\TESTTWO.NDF',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=15%
),
FILEGROUP FG_TESTTWO
(
NAME='FG_TESTTWO_NDF',
FILENAME='E:\DATABASE\FG_TESTTWO.NDF',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=10%

),
FILEGROUP FG2_TESTTWO
(
NAME='FG2_TESTTWO_NDF',
FILENAME='E:\DATABASE\FG2_TESTTWO.NDF',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=10%
)
LOG ON 
(
NAME='TESTTWO_log',
FILENAME='E:\DATABASE\TESTTWO.LOG',
SIZE=2MB,
MAXSIZE=4MB,
FILEGROWTH=10%
)
--------------------------------------------------------------------------
SELECT * FROM :: fn_helpcollations()  --查询排序
--------------------------------------------------------------------------
USE master
IF EXISTS (
  SELECT * 
    FROM sys.databases 
   WHERE name = N'TESTTHR'
)
  DROP DATABASE TESTTHR
GO
CREATE DATABASE [TESTTHR] ON  PRIMARY 
( 
 NAME = N'TESTTHR_MDF',
 FILENAME = N'E:\DATABASE\TESTTHR.MDF' , 
 SIZE = 10240KB , 
 MAXSIZE = 51200KB , 
 FILEGROWTH = 10%
 ), 
( 
NAME = N'TESTTHR_NDF',
 FILENAME = N'E:\DATABASE\TESTTHR.NDF' ,
 SIZE = 10240KB ,
 MAXSIZE = 51200KB ,
 FILEGROWTH = 10%
 ), 
 FILEGROUP [FG1] 
( 
 NAME = N'FG1_TESTTHR_NDF', 
 FILENAME = N'E:\DATABASE\FG1_TESTTHR.NDF' ,
 SIZE = 10240KB , 
 MAXSIZE = 51200KB ,
 FILEGROWTH = 10%
), 
(
 NAME = N'FG1_TESTTHR2_NDF',
 FILENAME = N'E:\DATABASE\FG1_TESTTHR2.NDF' , 
 SIZE = 10240KB ,
 MAXSIZE = 51200KB ,
 FILEGROWTH = 10%
), 
 FILEGROUP [FG2] 
( 
 NAME = N'FG2_TESTTHR_NDF', 
 FILENAME = N'E:\DATABASE\FG2_TESTTHR.NDF' ,
 SIZE = 10240KB ,
 MAXSIZE = 51200KB ,
 FILEGROWTH = 10%
)
 LOG ON 
(
 NAME = N'TESTTHR_LOG',
 FILENAME = N'E:\DATABASE\TESTTHR.LOG' ,
 SIZE = 10240KB ,
 MAXSIZE = 51200KB ,
 FILEGROWTH = 10%
), 
( 
 NAME = N'TESTTHR2_LOG', 
 FILENAME = N'E:\DATABASE\TESTTHR2.LOG' ,
 SIZE = 5120KB , 
 MAXSIZE = 25600KB , 
 FILEGROWTH = 5120KB 
 )
GO
--------------------------------------------------
ALTER DATABASE  TESTTRE   --修改数据库名称
MODIFY NAME =TESTTHR
GO

ALTER DATABASE TESTTHR  --向数据库中添加一个数据文件
ADD FILE
(
NAME=TESTTRE_NDF,
FILENAME='E:\DATABASE\TESTTRE.NDF',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=10%
);
ALTER DATABASE TESTTHR  --修改数据文件名称
MODIFY FILE
(NAME='TESTTRE_NDF',NEWNAME='TESTTRE2_NDF',FILENAME='E:\DATABASE\TESTTRE2.NDF')

ALTER DATABASE TESTTHR   --修改排序规则
COLLATE Chinese_PRC_CI_AS_KS

ALTER DATABASE TESTTHR   --在TESTTHR里删除一个数据文件
REMOVE FILE TESTTRE2_NDF

ALTER DATABASE TESTTHR
ADD FILEGROUP FG3 --添加一个文件组

ALTER DATABASE TESTTHR
MODIFY FILEGROUP FG3  --修改文件组的名称
NAME=FG4

ALTER DATABASE TESTTHR --添加一个文件到文件组
ADD FILE(NAME='TESTFOUR_NDF',FILENAME='E:\DATABASE\TESTFOUR.NDF')
TO FILEGROUP FG3
GO 
ALTER DATABASE TESTTHR
MODIFY FILEGROUP FG3 DEFAULT  --设置文件组为默认文件组
GO  

ALTER DATABASE TESTTHR
REMOVE FILEGROUP FG4
GO

ALTER DATABASE TESTTHR
MODIFY FILEGROUP [PRIMARY] DEFAULT
GO 

----------------------------------------------------------------------
USE master  
EXEC sp_detach_db TESTTHR --使用存储过程分离数据库

----------------------------------------------------------------------
USE master
CREATE DATABASE TESTTHR        --附加数据库
ON (FILENAME='E:\DATABASE\TESTTHR.MDF')
FOR ATTACH
GO

EXEC sp_attach_db TESTTHR, --使用存储过程附加数据库
'E:\DATABASE\TESTTHR.MDF',
'E:\DATABASE\TESTTHR.LOG'

-------------------------------------------------------------------------
USE master
ALTER DATABASE TESTTHR
SET OFFLINE ---------ONLINE 联机数据库，OFFLINE是脱机数据库

-----------------------------------------------------------------------------
USE master
DROP DATABASE TESTTHR

----------------------------------------------------------------------
DBCC SHRINKDATABASE (TESTTHR,20) --收缩数据库   自动收缩

USE TESTTHR
DBCC SHRINKFILE (TESTTHR_MDF,20) --收缩文件