Delete all your BizTalk databases at once

Sometimes, you screw up your BizTalk configuration, and rather than trying to fix it up, it’s better to just delete your databases and start afresh.

Here is a script that I use to do just that – it also closes your existing connections:

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'BAMArchive')
ALTER DATABASE BAMArchive SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE BAMArchive
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'BAMPrimaryImport')
ALTER DATABASE BAMPrimaryImport SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
DROP DATABASE BAMPrimaryImport
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'BAMStarSchema')
ALTER DATABASE BAMStarSchema SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
DROP DATABASE BAMStarSchema
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'BizTalkDTADb')
ALTER DATABASE BizTalkDTADb SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
DROP DATABASE BizTalkDTADb
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'BizTalkMgmtDb')
ALTER DATABASE BizTalkMgmtDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
DROP DATABASE BizTalkMgmtDb
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'BizTalkMsgBoxDb')
ALTER DATABASE BizTalkMsgBoxDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
DROP DATABASE BizTalkMsgBoxDb
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'BizTalkRuleEngineDb')
ALTER DATABASE BizTalkRuleEngineDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
DROP DATABASE BizTalkRuleEngineDb
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SSODB')
ALTER DATABASE SSODB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
DROP DATABASE SSODB
GO

Searching in Stored Procedures

A very convenient way to search for specific text in stored procedures is to use the view INFORMATION_SCHEMA.ROUTINES.
If you type this SQL query :

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

you’ll see a lot of information about the stores procedures of the database where you executed your query, including the code of the procedure.
Now if you have a lot of stored procedures, are looking for a specific word and need some answers quick, try this:

SELECT  ROUTINE_NAME, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE' AND  ROUTINE_DEFINITION LIKE '%yourTextHere%'
ORDER BY ROUTINE_NAME

You just need to Execute that and you got your answer!