- Home
- SQL Server
- SQL Server Blog
- Programmatically find MS Shipped Databases
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Hello,
Have you ever needed to programmatically find what databases in you server are part of Microsoft products? Maybe to use that information in your own piece of code?
In my case, I needed that information to exclude Sharepoint and Biztalk databases from the AdaptiveIndexDefrag cycles that are part of the maintenance operations we deploy in customers (implemented by the related job, not the procedure itself), and some other internal uses.
So with the help of several engineers around the globe, I have gathered a matrix of products and their databases. Because some products do not enforce a fixed database name, we also needed some notable schema objects that would allow us to identify those databases.
Here’s the result of that shared effort:
|
Product |
Database Name(s) |
Notable schema objects |
References |
|
ASP.NET Session State |
ASPState |
ASPStateTempSessions;
|
http://support.microsoft.com/kb/317604 http://msdn.microsoft.com/en-us/library/hh948178.aspx |
|
BizTalk 2004 |
BizTalkMgmtDb
|
BizTalkDBVersion |
http://msdn.microsoft.com/en-us/library/ee251618(v=bts.10).aspx |
|
BizTalk 2004 / 2006R2 / 2009 / 2010 |
SSODB |
SSOX_<variable> |
|
|
Biztalk 2006R2 |
BizTalkMgmtDb
|
BizTalkDBVersion |
http://msdn.microsoft.com/en-us/library/aa578342(v=bts.20).aspx |
|
BizTalk 2009 / 2010 / 2013 |
BizTalkMgmtDb
|
BizTalkDBVersion |
http://blogs.msdn.com/b/skesiraju/archive/2009/02/03/indentify-biztalk-version-and-edition.a... http://msdn.microsoft.com/en-us/library/aa578342.aspx http://msdn.microsoft.com/en-us/library/aa578342(v=bts.10).aspx http://msdn.microsoft.com/en-us/library/aa578342(v=bts.80).aspx |
|
Dynamics AX 2009, Dynamics AX 4.0 |
No specific name as it varies based on customer preference. |
LedgerTrans;
|
http://msdn.microsoft.com/en-us/library/aa624918(v=ax.50).aspx |
|
Dynamics CRM 2011 |
MSCRM_Config |
Organization;
|
http://msdn.microsoft.com/en-us/library/cc308176.aspx |
|
Dynamics CRM 4.0 / 2011 |
<variable>_MSCRM |
ContactBase;
|
http://msdn.microsoft.com/en-us/library/cc308176.aspx http://technet.microsoft.com/en-us/library/dd979327.aspx |
|
Dynamics GP 7.5 / 8 / 9 / 10 |
No specific name as it varies based on customer preference. |
DU000020;
|
http://support.microsoft.com/kb/850662 http://www.gptablereference.com/ |
|
Dynamics NAV 5.0 / 2009 |
No specific name as it varies based on customer preference. "Demo Database NAV (6-0)" - default logical file name |
User Personalization;
|
http://support.microsoft.com/kb/980653 http://www.microsoft.com/en-us/download/details.aspx?id=24432 |
|
FAST Search Server 2010 for SharePoint |
FASTSearchAdminDatabase |
http://technet.microsoft.com/library/cc678868(office.14).aspx |
|
|
Forefront Endpoint Protection 2010 |
FEPDW
|
http://technet.microsoft.com/en-us/library/gg710933.aspx |
|
|
Forefront Identity Management 2010 |
FIMService |
http://technet.microsoft.com/en-us/library/hh322891(v=ws.10) |
|
|
Lync 2010 |
xds
|
FocusJoinsAndLeaves;
|
http://technet.microsoft.com/en-us/library/gg398370(v=ocs.14).aspx |
|
Lync 2013 |
xds
No specific name for Persistent Chat database as it varies based on customer preference |
FocusJoinsAndLeaves;
|
http://technet.microsoft.com/en-us/library/gg398370.aspx |
|
MediaRoom |
BranchDB
|
SQLJobDefinition;
|
|
|
MediaRoom |
BranchDB
|
MasterInfo |
|
|
MediaRoom |
BranchDB
|
VodKeyRing |
|
|
PerformancePoint Services |
PerformancePoint Service Application_<variable>
|
http://technet.microsoft.com/library/cc678868(office.14).aspx |
|
|
PowerPivot |
DefaultPowerPivotServiceApplicationDB_<variable> |
http://technet.microsoft.com/library/cc678868(office.14).aspx http://technet.microsoft.com/en-us/library/cc678868.aspx#Sec2 |
|
|
Project Server 2010 |
ProjectServer_Draft
|
http://technet.microsoft.com/library/cc678868(office.14).aspx |
|
|
Project Server 2013 |
ProjectWebApp (Default - can be changed) |
http://technet.microsoft.com/en-us/library/cc678868.aspx#Sec2 |
|
|
SC Orchestrator 2012 |
Orchestrator |
MaintenanceTasks;
|
|
|
SCCM 2007 |
SMS_<variable> |
Agents;
|
http://www.microsoft.com/en-us/download/details.aspx?id=22052 |
|
SCCM 2012 |
CM_<variable> |
Agents;
|
http://msdn.microsoft.com/en-us/library/hh948178.aspx http://connect.microsoft.com/SQLServer/feedback/details/783327/trace-flag-8295 |
|
SCCM 2012 |
SUSDB |
||
|
SCOM 2007R2 / 2012 / 2012R2 |
OperationsManager
|
ManagedEntity;
|
http://technet.microsoft.com/en-us/library/gg508713.aspx |
|
SCSM 2010 |
ServiceManager
|
|
http://technet.microsoft.com/en-us/library/ff461229.aspx |
|
SCSM 2012 |
Service Manager
|
|
http://technet.microsoft.com/en-us/library/hh519598.aspx |
|
Sharepoint 2007 |
WSS_Search
|
Versions |
http://technet.microsoft.com/en-US/library/cc678868(v=office.12).aspx |
|
Sharepoint 2007 |
SSO |
http://technet.microsoft.com/en-US/library/cc678868(v=office.12).aspx |
|
|
Sharepoint 2010 |
SharePoint_Config
|
Versions |
http://technet.microsoft.com/library/cc678868(office.14).aspx |
|
SharePoint 2013 |
SharePoint_Config
|
Versions |
http://technet.microsoft.com/en-us/library/cc678868.aspx |
|
SQL 2012 Semantic Search |
Semanticsdb |
http://www.microsoft.com/en-us/download/details.aspx?id=29069 |
|
|
SSRS |
ReportServer |
ChunkData;
|
http://technet.microsoft.com/en-us/library/ms159093.aspx |
|
SSRS |
ReportServerTempDB |
ChunkData;
|
http://technet.microsoft.com/en-us/library/ms159093.aspx |
|
SSRS (Sharepoint Integrated Mode) |
ReportingService_<variable>
RSDB |
ChunkData;
|
http://technet.microsoft.com/en-us/library/ms157285.aspx http://technet.microsoft.com/library/cc678868(office.14).aspx#Section7 http://technet.microsoft.com/en-US/library/cc678868(v=office.15).aspx#Sec4 |
|
SSRS (Sharepoint Integrated Mode) |
ReportingService_<variable>TempDB
|
ChunkData;
|
http://technet.microsoft.com/en-us/library/ms157285.aspx http://technet.microsoft.com/library/cc678868(office.14).aspx#Section7 http://technet.microsoft.com/en-US/library/cc678868(v=office.15).aspx#Sec4 |
|
TFS2010 / TFS 2012 |
Tfs_Configuration |
tbl_AccessMapping;
|
http://msdn.microsoft.com/en-us/library/ms253070.aspx#TableDB |
|
TFS2010 / TFS 2012 |
Tfs_DefaultCollection
|
FenceConfiguration;
|
http://msdn.microsoft.com/en-us/library/ms253070.aspx#TableDB |
|
TFS2010 / TFS 2012 |
Tfs_Warehouse |
FactWorkItemLinkHistory;
|
http://msdn.microsoft.com/en-us/library/ms253070.aspx#TableDB |
Here’s a snippet using this information. First list all DBs that are already identifiable by name as not being a MS Shipped DB:
IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs'))
DROP TABLE #tmpdbs
IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs'))
CREATE TABLE #tmpdbs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, isdone bit)
INSERT INTO #tmpdbs ([dbid], [dbname], isdone)
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND [state] = 0 AND database_id > 4 AND is_distributor = 0
-- Ignore MS shipped databases
AND [name] NOT IN (
--Semantic Search
'Semanticsdb',
--SCSM 2012
'Service Manager','DWStagingAndConfig','DWRepository','DWDataMart','DWASDataBase','OMDWDataMart','CMDWDataMart',
--SCSM 2010
'ServiceManager',
--Biztalk 2009 / 2010 / 2013
'SSODB','BAMAnalysis','BAMArchive','BAMAlertsApplication','BAMAlertsNSMain','BAMPrimaryImport','BAMStarSchema','BizTalkMgmtDb','BizTalkMsgBoxDb','BizTalkDTADb','BizTalkRuleEngineDb',
--Biztalk 2006R2
'BAMPrimaryImport','BizTalkEDIDb','BizTalkHwsDb','TPM','BizTalkAnalysisDb',
--Biztalk 2004
'BAMPrimaryImportsuccessfully',
--ASP .NET
'ASPState',
--Dynamics CRM 2011
'MSCRM_Config',
--Forefront Endpoint Protection 2010
'FEPDW',
--Lync 2010
'xds','rtc','rtcdyn','rtcab','rtcab1','cpsdyn','rgsdyn','rgsconfig','lis','LcsLog','LcsCDR','QoEMetrics',
--Lync 2013
'lyss','rtcxds',
--MediaRoom
'BranchDB','vodBackend','ActivityLog','ServiceGroupDB','EventLog','ClientTraceLog','ListingsSettings','tServerController',
--SCOM 2007R2 / 2012 / 2012R2
'OperationsManager','OperationsManagerDW','OperationsManagerAC'
--SC Orchestrator 2012
'Orchestrator',
--Sharepoint 2007
'SSO','WSS_Search','WSS_Search_Config','SharedServices_DB','SharedServices_Search_DB','WSS_Content',
--SCCM 2007 / 2012
'SUSDB',
--Project Server
'ProjectWebApp',
--SSRS
'ReportServer','ReportServerTempDB',
--SSRS (Sharepoint Integrated Mode)
'RSDB','RSTempDB',
--TFS 2010 / 2012
'Tfs_Configuration','Tfs_DefaultCollection','Tfs_Warehouse'
)
--Dynamics CRM 2011
AND [name] NOT LIKE '%_MSCRM'
--Forefront Endpoint Protection 2010
AND [name] NOT LIKE 'FEPDB_%'
--SCCM 2007
AND [name] NOT LIKE 'SMS_%'
--SCCM 2012
AND [name] NOT LIKE 'CM_%'
--SharePoint Server 2007
AND [name] NOT LIKE 'WSS_Search%'
AND [name] NOT LIKE 'SharedServices_DB%'
AND [name] NOT LIKE 'SharedServices_Search_DB%'
AND [name] NOT LIKE 'SharedServices__DB%'
AND [name] NOT LIKE 'SharedServices__Search_DB%'
AND [name] NOT LIKE 'SharedServicesContent%'
--SharePoint 2010
AND [name] NOT LIKE 'Secure_Store_Service_DB_%'
AND [name] NOT LIKE 'StateService%'
AND [name] NOT LIKE 'WebAnalyticsServiceApplication_StagingDB_%'
AND [name] NOT LIKE 'WebAnalyticsServiceApplication_ReportingDB_%'
AND [name] NOT LIKE 'Search_Service_Application_DB_%'
AND [name] NOT LIKE 'Search_Service_Application_CrawlStoreDB_%'
AND [name] NOT LIKE 'Search_Service_Application_PropertyStoreDB_%'
AND [name] NOT LIKE 'User Profile Service Application_ProfileDB_%'
AND [name] NOT LIKE 'User Profile Service Application_SyncDB_%'
AND [name] NOT LIKE 'User Profile Service Application_SocialDB_%'
AND [name] NOT LIKE 'Managed Metadata Service_%'
AND [name] NOT LIKE 'WordAutomationServices_%'
--SharePoint 2013
AND [name] NOT LIKE 'SharePoint_Admin_Content%'
AND [name] NOT LIKE 'AppManagement%'
AND [name] NOT LIKE 'Search_Service_Application_AnalyticsReportingStoreDB_%'
AND [name] NOT LIKE 'Search_Service_Application_LinkStoreDB_%'
AND [name] NOT LIKE 'Secure_Store_Service_DB_%'
AND [name] NOT LIKE 'SharePoint_Logging_%'
AND [name] NOT LIKE 'SettingsServiceDB%'
AND [name] NOT LIKE 'SharePoint_Logging_%'
AND [name] NOT LIKE 'Managed Metadata Service Application_Metadata_%'
AND [name] NOT LIKE 'SharePoint Translation Services_%'
AND [name] NOT LIKE 'SessionStateService%'
--PerformancePoint Services
AND [name] NOT LIKE 'PerformancePoint Service_%'
--SharePoint Foundation 2010
AND [name] NOT LIKE 'SharePoint_Config%'
AND [name] NOT LIKE 'SharePoint_AdminContent%'
AND [name] NOT LIKE 'WSS_Content%'
AND [name] NOT LIKE 'WSS_UsageApplication%'
AND [name] NOT LIKE 'Bdc_Service_DB_%'
AND [name] NOT LIKE 'Application_Registry_server_DB_%'
AND [name] NOT LIKE 'SubscriptionSettings_%'
AND [name] NOT LIKE 'SharePoint_AdminContent%'
--SSRS SP Integrated Mode
AND [name] NOT LIKE 'ReportingService_%'
--Project Server 2010
AND [name] NOT LIKE 'ProjectServer_Draft%'
AND [name] NOT LIKE 'ProjectServer_Published%'
AND [name] NOT LIKE 'ProjectServer_Archive_%'
AND [name] NOT LIKE 'ProjectServer_Reporting%'
--FAST Search Server for SharePoint 2010
AND [name] NOT LIKE 'FASTSearchAdminDatabase%'
--PowerPivot for SharePoint
AND [name] NOT LIKE 'DefaultPowerPivotServiceApplicationDB%'
--Project Server
AND [name] NOT LIKE 'ProjectWebApp%'
--PerformancePoint Services
AND [name] NOT LIKE 'PerformancePoint Service_%'
--TFS 2010 / 2012
AND [name] NOT LIKE 'Tfs_%';
And then look for notable tables in remaining DBs:
IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblMSDBs'))
DROP TABLE #tblMSDBs;
IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblMSDBs'))
CREATE TABLE #tblMSDBs ([dbid] int, [dbname] sysname);
DECLARE @dbname sysname, @dbid int, @sqlcmd NVARCHAR(2000)
WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0
SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + ';
IF EXISTS (SELECT name AS [tab_name]
FROM sys.all_objects (NOLOCK) WHERE name IN (''ASPStateTempSessions'',''ASPStateTempApplications'',''BizTalkDBVersion'',''LedgerTrans'',
''InventTrans'',''InventSum'',''Organization'',''OrganizationProperties'',''Notification'',''ContactBase'',''ContactExtensionBase'',''AccountBase'',
''AccountExtensionBase'',''SQLJobDefinition'',''JobStepDefinition'',''JobScheduleDefinition'',''MasterInfo'',''VodKeyRing'',''ManagedEntity'',
''MaintenanceMode'',''MaintenanceTasks'',''TASK_RUNPROGRAM'',''Agents'',''DistributionPoints'',''DistributionPointGroup'',''SysResList'',
''Versions'',''ChunkData'',''ExecutionLogStorage'',''Subscriptions'',''SegmentedChunk'',''tbl_AccessMapping'',''tbl_CatalogResource'',
''tbl_NotificationQueue'',''FenceConfiguration'',''FencedLabSystemConfiguration'',''WorkItemsAre'',''WorkItemsWere'',''FactWorkItemLinkHistory'',
''DimToolArtifactDisplayUrl'',''FactWorkItemHistory'',''User Personalization'',''DU000020'',''PDK_SQL_Options'',''PDK_Version'',''SY_SQL_Options'',
''GPS_SQL_Error_Codes'',''FocusJoinsAndLeaves'',''MediationServers'',''ConferenceSessionDetails'',''AppliedBandwidthSource'',''PayloadDescription'',
''PrincipalMemberDifference'',''PrincipalAffiliations'',''tblPrincipalMemberDifference'',''tblPrincipalAffiliations'')
OR name LIKE ''SSOX_%'' OR name LIKE ''%$G_L Entry'' OR name LIKE ''%$%Ledger Entry'')
SELECT ''' + CONVERT(VARCHAR(10), @dbid) + ''' AS [DBID], ''' + @dbname + ''' AS [DBName]'
INSERT INTO #tblMSDBs
EXECUTE sp_executesql @sqlcmd
UPDATE #tmpdbs
SET isdone = 1
WHERE dbid = @dbid
END;
DELETE #tmpdbs
FROM #tmpdbs t
INNER JOIN #tblMSDBs tms ON t.[dbid] = tms.[dbid];
Finally, list all DBs in the system that were not identified as MS Shipped DBs:
SELECT dbid,dbname FROM #tmpdbs;
Many thanks to fellow engineers that helped gather much of the raw information behind this: Alex Meyer, Andres Naranjo, Arvind Shyamsundar, Carson Yeung, Chin Seng Goh, Gonçalo Antunes, João Oliveira, João Loureiro, Manuel Semblano, Marco Cibis, Rui Costa, Rui Veloso, Shaun Beane and Todd Haynie.
Until next time!
Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.