Quick View of Your Database

DATABASE:

• SELECT @@SERVERNAME   // Find the instance that you are currently connected to 
• SELECT @@VERSION          // Find The version of SQLSERVER that you have installed on your system.
• select * from sys.databases   // Lists all the databases in your instance and other information
• select * from sys.sysprocesses   //Lists all the processes that are running, their spids and blocked spids and other information
• select @@MAX_CONNECTIONS  // returns the number of user that can connect to your instance simultaneous for the version of SQLSERVER installed. It need not be the current number that is configured. You may use sp_configure to configure a number of your choice.
• select @@CONNECTIONS //list the attempted number of connection since the last start up of your database. The below query can also be used.
• SELECT GETDATE() AS 'Today''s Date and Time', @@CONNECTIONS AS 'Login Attempts'
• select @@SPID //returns the server process ID of the currently logged in user process.or the below query
• SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
• sp_who //provides information about the current user, sessions, processes. information on people logged in.

BACKUP INFORMATION:

• The below query fetches information on the date when the last backup was taken for all the databases in your instance.

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name