Bookmark and Share
How to find SQL instance name, server name, DB status
(Publish Date: 2014-7-30 4:31pm, Total Visits: 612, Today: 1, This Week: 3, This Month: 15)

1. find instance name and server name

SELECT
            SERVERPROPERTY('MachineName') AS [ServerName],
        SERVERPROPERTY('ServerName') AS [ServerInstanceName],
            SERVERPROPERTY('InstanceName') AS [Instance],
            SERVERPROPERTY('Edition') AS [Edition],
            SERVERPROPERTY('productlevel') AS [Productlevel],
            SERVERPROPERTY('ProductVersion') AS [ProductVersion],
            Left(@@Version, Charindex('-', @@version) - 2) As VersionName

Sample output:

ServerName    ServerInstanceName    Instance    Edition            Productlevel    ProductVersion    VersionName
S2KeastA01    S2KeastA01        NULL        Standard Edition    SP3        8.00.760    Microsoft SQL Server  2000

2. Find SQL server versions

SELECT @@VERSION AS 'SQL Server Version';
go

SELECT @@VERSION;
go

3. Check DB status

EXEC sp_helpdb;
GO

SELECT * FROM sys.databases
WHERE recovery_model_desc = 'SIMPLE';
GO