【www.bbyears.com--php常用代码】
注意事项：这个脚本只有2个参数需要输入，即：目标数据库名字和目标目录
例1
代码如下USE master
GO
DECLARE
@DBName sysname,
@DestPath varchar(256)
DECLARE @DB table(
name sysname,
physical_name sysname)
BEGIN TRY
SELECT
@DBName = "TargetDatabaseName", --input database name
@DestPath = "D:SqlData" --input destination path
-- kill database processes
DECLARE @SPID varchar(20)
DECLARE curProcess CURSOR FOR
SELECT spid
FROM sys.sysprocesses
WHERE DB_NAME(dbid) = @DBName
OPEN curProcess
FETCH NEXT FROM curProcess INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC("KILL " + @SPID)
FETCH NEXT FROM curProcess
END
CLOSE curProcess
DEALLOCATE curProcess
-- query physical name
INSERT @DB(
name,
physical_name)
SELECT
A.name,
A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
ON A.database_id = B.database_id
AND B.name = @DBName
WHERE A.type <=1
--set offline
EXEC("ALTER DATABASE " + @DBName + " SET OFFLINE")
--move to dest path
DECLARE
@login_name sysname,
@physical_name sysname,
@temp_name varchar(256)
DECLARE curMove CURSOR FOR
SELECT
name,
physical_name
FROM @DB
OPEN curMove
FETCH NEXT FROM curMove INTO @login_name,@physical_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @temp_name = RIGHT(@physical_name,CHARINDEX("",REVERSE(@physical_name)) - 1)
EXEC("exec xp_cmdshell ""move "" + @physical_name + "" "" + @DestPath + """"")
EXEC("ALTER DATABASE " + @DBName + " MODIFY FILE ( NAME = " + @login_name
+ ", FILENAME = """ + @DestPath + @temp_name + """)")
FETCH NEXT FROM curMove INTO @login_name,@physical_name
END
CLOSE curMove
DEALLOCATE curMove
-- set online
EXEC("ALTER DATABASE " + @DBName + " SET ONLINE")
-- show result
SELECT
A.name,
A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
ON A.database_id = B.database_id
AND B.name = @DBName
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO
例2
创建一个sqlserver数据库的代码模板，加上一个创建表的模板。开发的时候可以拷贝过去直接改动一下就可以用了
代码如下 USE [master]
GO
IF EXISTS(SELECT 1 FROM sysdatabases WHERE NAME=N"HkTemp")
BEGIN
DROP DATABASE HkTemp --如果数据库存在先删掉数据库
END
GO
CREATE DATABASE HkTemp
ON
PRIMARY --创建主数据库文件
(
NAME="HkTemp",
FILENAME="E:DatabasesHkTemp.dbf",
SIZE=5MB,
MaxSize=20MB,
FileGrowth=1MB
)
LOG ON --创建日志文件
(
NAME="HkTempLog",
FileName="E:DatabasesHkTemp.ldf",
Size=2MB,
MaxSize=20MB,
FileGrowth=1MB
)
GO
--添加表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N"Hksj_User") AND type in (N"U"))
BEGIN
CREATE TABLE Hksj_User
(
Id INT IDENTITY(1,1) NOT NULL,
SName NVARCHAR(20) NOT NULL,
SNickName NVARCHAR(20),
SPassWord NVARCHAR(30) NOT NULL,
DCreateDate DATETIME ,
SCreator NVARCHAR(20),
SEmail NVARCHAR(50),
SPhone NVARCHAR(50),
SIdentifyId NVARCHAR(30),
DLastTimeLogOn DATETIME
PRIMARY KEY CLUSTERED
(
Id ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
--添加注释
EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"登录名" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"Hksj_User", @level2type=N"COLUMN",@level2name=N"SName"
GO
EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"邮箱" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"Hksj_User", @level2type=N"COLUMN",@level2name=N"SEmail"