Menu
I know that so far (until MSSQL 2005 at least), system databases are master, model, msdb and tempdb.
Thing is, as far as I can tell, this is not guaranteed to be preserved in the future. And neither the sys.databases view nor the sys.sysdatabases view tell me if a database is considered as a system database.
Is there someplace where this information (whether a database is considered a system database or not) can be obtained?
357
System databases are the databases that are installed during the SQL Server installation. System databases consist of Master, MSDB, TempDB, and Model.
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. To see a list of all databases on the instance, expand Databases.
IF IS_SRVROLEMEMBER ('sysadmin') = 1 print 'Current user''s login is a member of the sysadmin role' ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0 print 'Current user''s login is NOT a member of the sysadmin role' ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL print 'ERROR: The server role specified is not valid.
Which of the following is not a system database? Explanation: Northwind is a sample database.
Just dived into Microsoft.SqlServer.Management.Smo.Database object (which is provided by Microsoft itself!)
They simply do this using following statement:
CAST(case when dtb.name in ('master','model','msdb','tempdb')
then 1
else dtb.is_distributor end AS bit) AS [IsSystemObject]
In short: if a database is named master, model, msdb or tempdb, it IS a system db;
it is also a system db, if field is_distributor = 1 in the view sys.databases.
Hope this helps
Jimmy
58
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
