Database Engine!

Question

Post reply

Database Engine!

subha.mcts2005

SSC Eights!

Points: 878
More actions
December 6, 2010 at 6:35 am

#229366

Hi,
how to sql engine identify the System & user databases ?
Subha

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Nakul Vachhrajani SSChampion Points: 10283 More actions · Answer 1

Does this help you?

http://www.sqlservercentral.com/Forums/Topic881813-338-1.aspx

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

subha.mcts2005 SSC Eights! Points: 878 More actions · Answer 2

Hi Nakul,

I didn't get the correct information.

plz Help me

Subha

Nakul Vachhrajani SSChampion Points: 10283 More actions · Answer 3

Hello!

Did you need help in identifying system & user databases?

For example, given a database name, do you want to tell if it is a system database or a user database?

If you can provide an example, that would be of great help.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Not sure what you're asking. Please clarify.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

Why do you care? What are you trying to do? If Paul White's script here doesn't do the job, what is that you're looking for?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

subha.mcts2005 SSC Eights! Points: 878 More actions · Answer 6

Hi Guys,

not my side identifying databases.

sql server Engine how to identify the databases ?

iam asking sql engine will consider any sid ?

Subha

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 7

The system databases are:

master, model, msdb, tempdb, ReportServer, ReportServerDB

You can identify them with an IN clause

...WHERE name not in ('master', 'model', 'msdb', 'tempdb', 'ReportServer', 'ReportServerDB')

Adiga One Orange Chip Points: 27284 More actions · Answer 8

subha.mcts2005 (12/6/2010)
Hi Guys,
not my side identifying databases.
sql server Engine how to identify the databases ?
iam asking sql engine will consider any sid ?

Like you query sys.databases to find database information, even the engine will refer to sys.databases to get the database names and any database which has database_id <=4 is considered a System Database.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 9

I guess I'm still confused as to what you're going for, but sQL Server knows which databases it uses to maintain itself. It works with them and there's very little you need to do beyond maintain backups of master & MSDB (model too, if you modify it).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 10

Steve Jones - SSC Editor (12/6/2010)
The system databases are:
master, model, msdb, tempdb, ReportServer, ReportServerDB
You can identify them with an IN clause
...WHERE name not in ('master', 'model', 'msdb', 'tempdb', 'ReportServer', 'ReportServerDB')

The problem is if you have SSRS installed, the Report Server/ReportServerDB will be larger than 4.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Steve Jones - SSC Editor (12/6/2010)
The system databases are:
master, model, msdb, tempdb, ReportServer, ReportServerDB
You can identify them with an IN clause
...WHERE name not in ('master', 'model', 'msdb', 'tempdb', 'ReportServer', 'ReportServerDB')

As far as I know, the two reportserver databases are user databases. They don't appear in the system database folder. If you argue those are system, then distribution must be classified system too.

There's also the hidden database - systemresource. Also system.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

subha.mcts2005 (12/6/2010)
sql server Engine how to identify the databases ?
iam asking sql engine will consider any sid ?

Why is this important to you? What's the reasoning behind this question?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass