SQL: General Database Info
Author: jason
Date: 2014-01-31 12:53:35
Category: Musing

Expanding a little bit on some earlier posts about general info, system info, etc, here are a couple more DB stored procedures.

The procedure sp_databases will return a list of databases attached to the server.

The procedure sp_helpDB DatabaseName will give some info about the database specified. It will also output sp_helpfile results, which are the file info.





sys.databases: http://technet.microsoft.com/en-us/library/ms178534.aspx
sp_databases: http://technet.microsoft.com/en-us/library/ms176070.aspx

Code

EXEC sp_databases;
EXEC sp_helpDB MyTest


Here is a script if you wanted to check to see if a database was attached and in an ONLINE mode to perform an action, like ALTER or whatever.





Code

DECLARE @DBName VARCHAR(50) = 'MyTest';
DECLARE @Online VARCHAR(50) = CAST((SELECT DATABASEPROPERTYEX(@DBName, 'Status')) AS VARCHAR(50));
IF DB_ID(@DBName) IS NOT NULL OR @Online = 'ONLINE' BEGIN
PRINT 'present and online'
END ELSE BEGIN
print 'not present or not online'
END





jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009