[Recommended] (SqlServer) separation of all user Databases

[Recommendation] (SqlServer)Detach all user Databases

--Establish a personal brand through knowledge sharing.

In practical applications, we sometimes need to separate all user databases at one time. The following code is provided for your reference.

 

USE [master]
GO
If exists (SELECT *
FROM sys. objects
WHERE [object_id] = OBJECT_ID (n' [dbo]. [spDetachAllUserDatabases] ')
AND type IN (n'p', n'pc '))
Drop procedure [dbo]. [spDetachAllUserDatabases]
GO

Create procedure [dbo]. [spDetachAllUserDatabases]
AS
BEGIN
-- Declare Variables
DECLARE @ DatabaseName VARCHAR (100)
DECLARE @ MinDatabaseID INT
DECLARE @ MaxDatabaseID INT
DECLARE @ SQL VARCHAR (4000)
-- Check for temporary table and drop it if it exists
IF OBJECT_ID ('tempdb. dbo. # database') IS NOT NULL
Drop table [# Database];

-- Create temporary table
Create table # Database
(
Id int identity (1, 1 ),
Database ename VARCHAR (100)
)

-- Check for existing user databases
If exists (SELECT name
FROM sys. databases
WHERE database_id> 4
AND name not in ('sqldb', 'reportserver ',
'Reportservertempdb ',
'Stribucket '))
BEGIN
-- Insert all database names into a temporary table
Insert into # Database (DatabaseName)
SELECT name
FROM sys. databases
WHERE database_id> 4
AND name not in ('sqldb', 'reportserver ',
'Reportservertempdb ',
'Stribucket ')

-- Set Variables for the detach database loop
SELECT @ MinDatabaseID = MIN (ID ),
@ MaxDatabaseID = MAX (ID)
FROM # Database

-- Begin loop to detach databases
WHILE @ MinDatabaseID <= @ MaxDatabaseID
BEGIN

-- Get DatabaseName
SELECT @ DatabaseName = DatabaseName
FROM # Database
Where id = @ MinDatabaseID

-- Build Detach Database Command
SET @ SQL = 'exec sp_detach_db '+ ''' + @ DatabaseName
+ ''' + ';'

-- Try Catch block to execute SQL and handle errors limit
BEGIN TRY

-- Detach Database
EXEC (@ SQL
)
PRINT 'detached' + @ DatabaseName
END TRY
BEGIN CATCH
SELECT @ DatabaseName,
Message_id,
Severity,
[Text],
@ SQL
FROM sys. messages
WHERE message_id = @ ERROR
AND language_id = 1033 -- British English
END CATCH

-- Get the next DatabaseName ID
SET @ MinDatabaseID = @ MinDatabaseID + 1

-- End Loop
END
END
END

GO

 

 

2011EricHu

For original works, please refer to the author and source for the post. Leave this information.

 

------------------------------------------------

CnBlobs:Http://www.cnblogs.com/huyong/
CSDN:Http://blog.csdn.net/chinahuyong

 

Author: EricHu (DB, C \ S, B \ S, WebService, WCF, PM, etc)
Source: http://www.cnblogs.com/huyong/

Q: 80368704 E-Mail: 80368704@qq.com
You are welcome to browse and repost this blog post, but this statement must be retained without the author's consent, and the original article connection is clearly provided on the article page. In the reference article, I will indicate the source of the reference article and respect the copyright of others. If you find that I infringe your copyright, please contact me in time.
For more articles, see[Pin to top] index stickers-(continuously updating)