[SQL Server]使用T-SQL取得資料庫File與File Group的實體路徑

2009-10-19

22406
0
MS SQL

使用T-SQL取得資料庫File與File Group的實體路徑

什麼是File Group呢,可以先參考：檔案與檔案群組說明

--建立一個'puma'DB

USE [master]
GO

CREATE DATABASE [puma] ON  PRIMARY 
( NAME = N'puma', FILENAME = N'D:\DB\puma.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [puma_fg] 
( NAME = N'puma_fg', FILENAME = N'D:\DB\puma_fg.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'puma_log', FILENAME = N'D:\DB\puma_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

--查詢'puma'DB的存放路徑

SELECT A.NAME,A.FILENAME FROM SYS.SYSALTFILES A
JOIN SYS.SYSDATABASES B ON A.DBID = B.DBID
WHERE B.NAME = 'puma'

--關閉現有的連接,並且刪除'puma'DB

USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'puma')
BEGIN
	ALTER DATABASE puma SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
	DROP DATABASE puma
END
GO

參考網址:
http://msdn.microsoft.com/zh-tw/library/ms179316.aspx
http://sharepointmalaya.blogspot.com/2009/04/close-current-connection-to-database.html

回首頁