Get non-system DB names-CodePudding

ALL,

I just tried the query found here, but it gave me all names.

How do I filter out system databases names?

TIA!

EDIT:

The suggested solutions contains hardcoded values, either names or hardcoded number. However, those are different depending on some other parameters.

CodePudding user response：

Until Microsoft will add is_ms_shipped column to the sys.databases system view, there will be no complete answer to your question.

The 4 default system databases have already been mentioned earlier. Their names are locked, so you can hardcode them pretty safely. Other databases not created explicitly by users are (the answer is valid for up to SQL Server 2019, and possibly 2022 versions):

Replication distributor: has sys.databases.is_distributor = 1
SSISDB: there is no simple solution here, as afaik the database name can be changed during SSIS installation. Your best bet is probably hardcoding the default.

Things that are not user databases in the strict sense:

Database shapshots: have sys.databases.source_database_id is not null
Schema and statistics-only copy of a database: the query select databasepropertyex('MyDbName', 'IsClone') returns 1.

NB: Because Microsoft adds new features in every major version of SQL Server, this answer should not be considered 100% complete in the future.

CodePudding user response：

There have typically been 4 system databases in a normal sql sever install for the last couple of decades.

These are master, model, msdb and tempdb and they have used IDs 1 through 4.

There are other system databases that you may run into, distribution or SSIDDB for example. These do not follow any particular ID patterns as it can depend on when they were installed, and what may have been created on that server prior to the installation.

For the most part, ignoring IDs 1 though 4 will, on most versions of SQL Server ignore those 4 system databases, but this doesn't mean there are not additional ones installed.