Identifying System/User Databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Identifying System/User Databases

Author

Topic

Peters
Starting Member

1 Post

Posted - 2002-11-12 : 08:36:09

Hi,

i´m looking for TSQL-Code (7.0/2000) to identify, if a SQL Server Database ist a SystemDatabase or a UserDatabase. In the sysdatabases there is no information abount that.

Skol,
Peter

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-12 : 09:16:37

There's no way for databases that I know of. Just out of curiosity, what would be the use of this? Can you not use the names eg 'master', 'tempdb', 'msdb', 'model'??

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-11-12 : 16:29:51

Here's a way in 2000 using a UDF (easy enough to convert to a proc in SQL 7). It's not pretty but it works, I thought there would be a DATABASEPROPERTX function but it appears not - having said that,it's not exactly the end of the world to hard code the names (not forgetting replication). I'd actually guess that SQLDMO has got the system dbnames hardcoded anyway

- I will try and find out


select CATALOG_NAME
from INFORMATION_SCHEMA.SCHEMATA
where dbo.IsSystemDB(CATALOG_NAME)=0  /* 1 = System , 0 =User */


CREATE FUNCTION dbo.IsSystemDB(@dbname sysname)
RETURNS INT /* 1 = System , 0 = User */
AS
BEGIN

DECLARE @hr int
DECLARE @sql int
DECLARE @db int
DECLARE @status int
DECLARE @system varchar(5)
DECLARE @dbcmd varchar(200)

SET @dbcmd = 'Databases("'+@dbname+'").SystemObject'

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @sql OUTPUT
IF @hr<>0 EXEC sp_OAGetErrorInfo @sql

EXEC @hr = sp_OASetProperty @sql ,'LoginSecure','True'
IF @hr<>0 EXEC sp_OAGetErrorInfo @sql

EXEC @hr = sp_OAMethod @sql,'Connect',NULL,@@SERVERNAME
IF @hr<>0 EXEC sp_OAGetErrorInfo @sql

EXEC @hr = sp_OAGetProperty @sql, @dbcmd, @system OUTPUT
IF @hr<>0 EXEC sp_OAGetErrorInfo @sql

EXEC @hr = sp_OAMethod @sql,'DisConnect',NULL
IF @hr<>0 EXEC sp_OAGetErrorInfo @sql

EXEC @hr=sp_OADestroy @sql
IF @hr<>0 EXEC sp_OAGetErrorInfo @sql

SELECT @status = CASE WHEN UPPER(@system)='TRUE'
                      THEN 1 ELSE 0 END

RETURN @status
END

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources