Programmatically find MS Shipped Databases

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:

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.