How to filter system database in SQL server?

Question

0.00/5 (No votes)

See more:

I want enum user database in SQL server, e.g.

select name from sys.databases

It returns below content:

master
tempdb
model
msdb
ReportServer$SQLEXPRESS
ReportServer$SQLEXPRESSTempDB
xsxk
MyDatabase2

The first 6 databases are system database, they are not my wanted.
I just need the last 2 databases. The last 2 databases are user created.

Please provide source code in C# or SQL, to filter system database.
The user database name has no rules.

What I have tried:

Using SMO:

Microsoft.SqlServer.Management.Smo.Server so = new Microsoft.SqlServer.Management.Smo.Server();
Microsoft.SqlServer.Management.Smo.DatabaseCollection dc = so.Databases;

It also return all databases.

Posted 17-May-17 18:16pm

w14243

Updated 22-May-17 23:44pm

Add a Solution

Comments

PIEBALDconsult 18-May-17 1:08am

See also:
https://www.codeproject.com/Questions/694915/How-to-determine-which-databases-are-and-are-not-s

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-05-18T23:01:00

Unfortunately if you base your query on a date then tempdb will (usually) still appear in your list (depending on how the instance is being managed).

If you use the standard approach

select [name] from sys.databases where database_id > 4

then (in your case) you will still get entries for ReportServer$SQLEXPRESS and ReportServer$SQLEXPRESSTempDB. You can't just change the WHERE clause to

SQL

where database_id > 6

because if you don't have Reporting services installed then you'll wrongly omit some genuine user created databases.

It may look as if you could use

SQL

select * from sys.databases where owner_sid = 0x01

i.e. list all databases created by the admin account. BUT what if your DBA used that account to create databases on behalf of a user? Again, you could wrongly omit some genuine databases of interest.

Probably the safest route is

SQL

select * from sys.databases where database_id > 5 and [name] NOT LIKE 'ReportServer%'

pradiprenushe · Answer 2 · 2017-05-17T18:48:00

Solution 1

In master db hit this query

(SQL 2012)

select * from sysdatabases order by crdate

Check date of first database created.
Make configuration for date with value equal to first user created DB
Now use below query to fetch your database

select * from sysdatabases where crdate > @date order by crdate

@date value should be fetched from configuration

Dont hard code @date in SP because you DEV,QC,UAT,Prod environment db created date will be different

Posted 17-May-17 18:48pm

pradiprenushe

Updated 17-May-17 18:50pm

v2

Comments

pradiprenushe 18-May-17 6:30am

Any reason for downvote

CHill60 19-May-17 4:49am

I should imagine it's because it doesn't quite work - check out database msdb - It's *that* date that you need. On all my SQL instances model and master are dated 2003-04-09. msdb is dated when I installed the instance on the computer. I suspect the ReportServer entries will have the same issue.
By the way, if you use sys.databases rather than sysdatabases then you can use the technique on earlier versions of SQL Server.

pradiprenushe 23-May-17 1:41am

date is same for system database and it will same when as installation date. But you will get different date for custom DB and that date you can use for logic. I have tested this on my side

CHill60 23-May-17 4:50am

You can test it as much as you want - the classic response from "developers" has always been "it works on my machine". I have given you an example of msdb having a different date to the installation date of the SQL Server instance. ReportServer* could have a completely different date again and the date on tempdb depends on how SQL Server is managed - mine for example is dated last month yet I installed SQL Server on this machine 3 years ago.
It wasn't me that downvoted you by the way, I was just responding to your question "Any reason for downvote"