July 15, 2005 at 4:28 am
Quick question for you all.
What do you think the easiest way is of determining which databases are System and which are User on any particular server is with T-SQL?
I'm writing a sproc to pull back database info and properties and I need to be able to pull back a list of either User or System databases (depending on the input I receive), preferably without selecting on system tables such as master.dbo.sysdatabases. I have exhausted all avenues I can think of, System Stored Procedures, System Functions and INFORMATION_SCHEMA_VIEWS but with no luck. I'm sure I saw some time ago a master system table that had a column that depicted the database type, i.e. U or S, but I've looked through BOL and the SQL 2K System Table Map and can’t find anything.
The only options I can think of are to do a frig and adjust my WHERE clause to pull out all databases with a dbid of =<4 for system databases and >4 for user databases, but this will break if something like replication is configured on a server. Or just check each database name against a static list including all known system databases?
Anyone have any other ideas?
Many Thanks in advance
lloyd
July 15, 2005 at 4:49 am
Apart from msdb, model, master and tempdb, what 'System' databases do you have in mind?
Regards
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 15, 2005 at 5:04 am
Phil,
I was just looking ahead to the use of these scripts in environments where replication is setup but am now unsure why this would be an issue as replication is handled within the master database and the associated publishers and subscribers and distributor databases/servers. I have gone for the quick fix and done along the lines of this:
SELECT .... WHERE helpdb.dbname IN
('master', 'msdb', 'model', 'tempdb')
thanks,
ll
July 18, 2005 at 12:54 pm
If you can't assume the default distribution database was used, the distribution databases are listed in msdb..MSdistributiondbs. You could use:
SELECT name FROM master.dbo.sysdatabases WHERE dbid <= 4
UNION ALL SELECT name FROM msdb.dbo.MSdistributiondbs
You're querying system tables, but it works (for now). I'd be careful about relying on "dbid <= 4"; the latest documentation (BOL 2003) says there is a new system db named mssqlweb (only on new SQL installs? I don't have one on any of my servers). It may be more robust to use a fixed list of names rather than dbid range.
If you really do not want to query system tables directly, you can get database names from sp_databases. You can get distribution database names from sp_helpdistributiondb, but otherwise I can't find anything in DATABASEPROPERTY or DATABASEPROPERTYEX that would let you identify user/system databases.
PS: I'm relying on BOL, I don't currently have a replicated system to check for the existence of msdb.dbo.MSdistributiondbs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply