Programmatically find MS Shipped Databases

Published Mar 23 2019 01:22 PM 186 Views
Microsoft
First published on MSDN on Oct 27, 2013

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;
ASPStateTempApplications



http://support.microsoft.com/kb/317604


http://msdn.microsoft.com/en-us/library/hh948178.aspx



BizTalk 2004



BizTalkMgmtDb
BizTalkDTADb
BizTalkMsgBoxDb
BizTalkRuleEngineDb
BAMStarSchema
BAMArchive
BAMAnalysis
BAMAlertsApplication
BAMAlertsNSMain
BizTalkAnalysisDb
BizTalkHwsDb
BizTalkEDIDb
BAMPrimaryImportsuccessfully
TPM



BizTalkDBVersion



http://msdn.microsoft.com/en-us/library/ee251618(v=bts.10).aspx



BizTalk 2004 / 2006R2 / 2009 / 2010



SSODB



SSOX_<variable>



Biztalk 2006R2



BizTalkMgmtDb
BizTalkDTADb
BizTalkMsgBoxDb
BizTalkRuleEngineDb
BAMStarSchema
BAMArchive
BAMAnalysis
BAMAlertsApplication
BAMAlertsNSMain
BizTalkAnalysisDb
BizTalkHwsDb
BizTalkEDIDb
BAMPrimaryImport
TPM



BizTalkDBVersion



http://msdn.microsoft.com/en-us/library/aa578342(v=bts.20).aspx



BizTalk 2009 / 2010 / 2013



BizTalkMgmtDb
BizTalkDTADb
BizTalkMsgBoxDb
BizTalkRuleEngineDb
BAMPrimaryImport
BAMStarSchema
BAMArchive
BAMAnalysis
BAMAlertsApplication
BAMAlertsNSMain



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;
InventTrans;
InventSum



http://msdn.microsoft.com/en-us/library/aa624918(v=ax.50).aspx



Dynamics CRM 2011



MSCRM_Config



Organization;
OrganizationProperties;
Notification



http://msdn.microsoft.com/en-us/library/cc308176.aspx



Dynamics CRM 4.0 / 2011



<variable>_MSCRM



ContactBase;
ContactExtensionBase;
AccountBase;
AccountExtensionBase;



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;
PDK_SQL_Options;
PDK_Version;
SY_SQL_Options;
GPS_SQL_Error_Codes;



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;
<identifier>$G_L Entry;
<identifier>$Item Ledger Entry;
<identifier>$Bank Account Ledger Entry



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
FEPDB_<variable>



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
rtc
rtcdyn
rtcab
rtcab1
cpsdyn
rgsdyn
rgsconfig
lis
LcsLog
LcsCDR
QoEMetrics

No specific name for Group Chat database as it varies based on customer preference



FocusJoinsAndLeaves;
MediationServers;
ConferenceSessionDetails;
AppliedBandwidthSource;
PayloadDescription;
PrincipalMemberDifference;
PrincipalAffiliations


http://technet.microsoft.com/en-us/library/gg398370(v=ocs.14).aspx

Lync 2013



xds
rtc
rtcshared
rtcxds
rtcdyn
rtcab
rtcab1
cpsdyn
rgsdyn
rgsconfig
lis
lyss
LcsLog
LcsCDR
QoEMetrics


No specific name for Persistent Chat database as it varies based on customer preference



FocusJoinsAndLeaves;
MediationServers;
ConferenceSessionDetails;
AppliedBandwidthSource;
PayloadDescription;
tblPrincipalMemberDifference;
tblPrincipalAffiliations


http://technet.microsoft.com/en-us/library/gg398370.aspx

MediaRoom



BranchDB
vodBackend
ActivityLog
ServiceGroupDB
EventLog
ClientTraceLog
ListingsSettings
tServerController



SQLJobDefinition;
JobStepDefinition;
JobScheduleDefinition



MediaRoom



BranchDB
ActivityLog
ServiceGroupDB
ClientTraceLog



MasterInfo



MediaRoom



BranchDB
ServiceGroupDB



VodKeyRing



PerformancePoint Services



PerformancePoint Service Application_<variable>
PerformancePoint Service_<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
ProjectServer_Published
ProjectServer_Archive_<variable>
ProjectServer_Reporting



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;
TASK_RUNPROGRAM



SCCM 2007



SMS_<variable>



Agents;
DistributionPoints;
DistributionPointGroup;
SysResList



http://www.microsoft.com/en-us/download/details.aspx?id=22052



SCCM 2012



CM_<variable>



Agents;
DistributionPoints;
DistributionPointGroup;
SysResList



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
OperationsManagerDW
OperationsManagerAC



ManagedEntity;
MaintenanceMode



http://technet.microsoft.com/en-us/library/gg508713.aspx



SCSM 2010



ServiceManager
DWRepositoryDWStagingAndConfig
DWRepository
DWDataMart





http://technet.microsoft.com/en-us/library/ff461229.aspx



SCSM 2012



Service Manager
DWStagingAndConfig
DWRepository
DWDataMart
DWASDataBase
OMDWDataMart
CMDWDataMart





http://technet.microsoft.com/en-us/library/hh519598.aspx



Sharepoint 2007



WSS_Search
WSS_Search_Config
SharedServices_DB
SharedServicesN_DB
SharedServices_Search_DB
SharedServicesN_Search_DB
WSS_Content
SharePoint_Admin_Content_<variable>



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
SharePoint_Admin_Content
WSS_Content
WSS_UsageApplication
Bdc_Service_DB_<variable>
Application_Registry_server_DB_<variable>
SubscriptionSettings_<variable>
Secure_Store_Service_DB_<variable>
StateService
WebAnalyticsServiceApplication_StagingDB_<variable>
WebAnalyticsServiceApplication_ReportingDB_<variable>
Search_Service_Application_DB_<variable>
Search_Service_Application_CrawlStoreDB_<variable>
Search_Service_Application_PropertyStoreDB_<variable>
User Profile Service Application_ProfileDB_<variable>
User Profile Service Application_SyncDB_<variable>
User Profile Service Application_SocialDB_<variable>
Managed Metadata Service_<variable>
WordAutomationServices_<variable>



Versions



http://technet.microsoft.com/library/cc678868(office.14).aspx



SharePoint 2013



SharePoint_Config
SharePoint_Admin_Content
WSS_Content
AppManagement
Bdc_Service_DB_<variable>
Search_Service_Application_Db_<variable>
Search_Service_Application_AnalyticsReportingStoreDB_<variable>
Search_Service_Application_CrawlStoreDB_<variable>
Search_Service_Application_LinkStoreDB_<variable>
Secure_Store_Service_DB_<variable>
SharePoint_Logging
SettingsServiceDB
User Profile Service Application_ProfileDB_<variable>
User Profile Service Application_SyncDB_<variable>
User Profile Service Application_SocialDB_<variable>
WordAutomationServices_<variable>
Managed Metadata Service Application_Metadata_<variable>
SharePoint Translation Services_<variable>
SessionStateService_<variable>



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;
ExecutionLogStorage;
Subscriptions;
SegmentedChunk



http://technet.microsoft.com/en-us/library/ms159093.aspx



SSRS



ReportServerTempDB



ChunkData;
ExecutionCache;
SegmentedChunk



http://technet.microsoft.com/en-us/library/ms159093.aspx



SSRS (Sharepoint Integrated Mode)



ReportingService_<variable>
ReportingService_<variable>_Alerting


RSDB



ChunkData;
ExecutionLogStorage;
Subscriptions;
SegmentedChunk



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

RSTempDB



ChunkData;
ExecutionCache;
SegmentedChunk



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;
tbl_CatalogResource;
tbl_NotificationQueue



http://msdn.microsoft.com/en-us/library/ms253070.aspx#TableDB



TFS2010 / TFS 2012



Tfs_DefaultCollection
Tfs_<variable>



FenceConfiguration;
FencedLabSystemConfiguration;
WorkItemsAre;
WorkItemsWere



http://msdn.microsoft.com/en-us/library/ms253070.aspx#TableDB



TFS2010 / TFS 2012



Tfs_Warehouse



FactWorkItemLinkHistory;
DimToolArtifactDisplayUrl;
FactWorkItemHistory



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.

Version history
Last update:
‎Mar 23 2019 01:22 PM
Updated by: