May 18, 2009 at 5:31 am
Hi group. I'm trying to find a way to show user databases in MSSQL 2008 - but none of the methods I know (and can find) differentiates between system and user databases.
I have checked sys.databases, sys.sysdatabases, sp_databases and sys.master_files - and still haven't found a field or parameter that let's me select user databases alone (excluding master, model, msdb and tempdb). I can do the exclusion by name but I would have thought that there was a more adequate method for this.
The closest I can get is to look at the "sid" column in sys.sysdatabases - where the apperant value '0x01' supposedly indicates a system database. But even if this value is a valid sign of a system database, it seems that I'm not allowed to use the value in a where-clause:
select * from sys.sysdatabases
WHERE sid = '0x01'
- yields no records.
Any thoughts?
May 18, 2009 at 5:33 am
What exactly would you like to do?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 18, 2009 at 5:54 am
I would like to fire up a T-SQL query that gave me more or less the output I get from "select * from sys.databases" - but without the system databases.
In short, I would like to do a query like:
SELECT *
FROM sys.databases
WHERE is_system_database = 0
- but I can't find the "is_system_database" field (or anything else giving me the same kind of information)
May 18, 2009 at 6:19 am
SELECT *
FROM sys.databases
WHERE database_id > 4
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
May 18, 2009 at 6:20 am
select * from sys.databases where database_id > 4
---------------------------------------------------------------------
May 18, 2009 at 6:23 am
jensgc (5/18/2009)
The closest I can get is to look at the "sid" column in sys.sysdatabases - where the apperant value '0x01' supposedly indicates a system database. But even if this value is a valid sign of a system database, it seems that I'm not allowed to use the value in a where-clause
Not at all. The SID column in sysdatabases is the security identifier of the owner of the database, 0x01 being the login 'sa'
So, a query that filters for that will return all DBs that are owned by sa, not just the system databases.
select *
from sys.databases
where owner_sid = 0x01
On my instance returns 5 rows, as one of my user databases is owned by sa. On a previous system I worked with it would have returned all databases, as policy dictated that DBs had to be owned by sa.
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
May 18, 2009 at 7:02 am
Thanks for the replies - using the database_id seems to be a solid workaround (even though it still puzzles me that there is no regular field to display system vs. user db).
And yes - using the sid on the other hand is not a good workaround since the sa user as mentioned could be the owner/creator of regular databases as well as system databases.
May 18, 2009 at 3:23 pm
I needed to do this for an audit, only user DBs, looks like SQL management Studio does it like this, cobble from the enture script, we have replication so needed to account for the distribution database.
select name,
(CAST(case when name in ('master','model','msdb','tempdb') then 1 else category & 16 end AS bit))
as IsSystemDatabase, dbid
from sysdatabases (nolock)
Andrew
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply