2011年3月1日 星期二

一次刪掉大量的Database和User

今天遇到了一個狀況,要一次刪上百個Database及上百個User,雖然手動一個一個刪也OK,但...我好懶,而且以後可能還會再遇到同樣的事,所以我寫了以下的SQL:
ps.本來是要用Cursor,因為涉及要一次取一個值,但...後來想想,還是算了,怕未來SQL Server不支援.


/*-----------------刪除大量的資料庫--------------------*/
--在sys.databases中有所有database的清單,
--透過@minDataBase_ID和@maxDataBase_ID介
--定Range(database_id有遞增的特性,且編號
--可重複使用)
--Important:"[9936018]UDT"->在刪除時格式為:
-- DROP DATABASE [[9936018]]UDT]
--所以不建議學生以"[9936018]UDT"格式命名
DECLARE @dataBaseName AS NVARCHAR(100)
DECLARE @minDataBase_ID AS INT
DECLARE @maxDataBase_ID AS INT

SET @minDataBase_ID=7--先在sys.databases中找出要刪除的下限

SELECT TOP 1 @maxDataBase_ID=database_id
FROM sys.databases
ORDER BY database_id DESC

WHILE(@minDataBase_ID<=@maxDataBase_ID)
BEGIN

SELECT @dataBaseName=sys.databases.name
FROM sys.databases
WHERE sys.databases.database_id=@minDataBase_ID

IF(LEN(@dataBaseName)>0)
BEGIN
PRINT @dataBaseName
EXEC('DROP DATABASE ['+@dataBaseName+']')
END

SET @minDataBase_ID=@minDataBase_ID+1
END
GO



/*-------------------刪除大量的User----------------------*/
--在sys.database_principals中有所有User的清單,
--透過@minUserID和@maxUserID介定Range
--Important:
--1.在撰寫時,有找到兩個View:sys.database_principals,
--sys.server_principals,一個是database-level一個是
--server-level
--2.DROP USER後,還要在DROP LOGIN,否則Account還在
DECLARE @userName AS NVARCHAR(100)
DECLARE @minUserID AS INT
DECLARE @maxUserID AS INT

SET @minUserID=10

SELECT TOP 1 @maxUserID=sys.database_principals.principal_id
FROM sys.database_principals
ORDER BY sys.database_principals.principal_id DESC

WHILE(@minUserID<=@maxUserID)
BEGIN

SELECT @userName=sys.database_principals.name
FROM sys.database_principals
WHERE sys.database_principals.principal_id=@minUserID

IF(LEN(@userName)>0)
BEGIN
EXEC('DROP USER ['+@userName+']')
EXEC('DROP LOGIN ['+@userName+']')
END

SET @minUserID=@minUserID+1
END
GO

沒有留言: