求适用于所有MS SQL数据库的通用监控查询语句（支持传参）

Great question! Let's break this down for your monitoring tool needs with MS SQL Server:

1. Yes, universal system objects exist across all MS SQL instances #

MS SQL Server comes with built-in system views and tables that are present in every instance (from SQL Server 2000 up to the latest versions, including Express, Standard, and Enterprise editions). The most reliable ones for connectivity testing are:

• sys.databases: Available in SQL Server 2005+, stored in the master database. It lists all databases on the instance.
• sys.sysdatabases: A backward-compatible system table (works even in SQL Server 2000) that serves the same purpose as sys.databases.

These are ideal because you don't need to switch to a user database to query them—you can run the check directly against the master database, which is always present on any MS SQL instance.

2. Universal query statements for connectivity testing #

Option 1: Simple instance connectivity check (no database parameter needed) #

If you just need to confirm the connection to the SQL instance works (regardless of a specific user database), use this lightweight query:

SELECT 1 AS TestResult;

Or for a more thorough check that verifies you can read system objects:

SELECT name FROM sys.databases WHERE database_id = 1; -- Returns 'master' if successful

Your monitoring tool can execute this query: if it receives any valid result set, return DB - Ok; if it throws an error (connection failure, permission denied, etc.), return DB - fail.

Option 2: Check connectivity to a specific database (with parameter) #

If your tool passes a database name as a parameter, use this approach to verify both the instance connection and access to the target database:

-- Replace @DatabaseName with your input parameter
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
BEGIN
    -- Verify access to the target database's system objects
    SELECT 1 AS TestResult FROM [@DatabaseName].sys.tables;
END
ELSE
BEGIN
    -- Return a failure indicator if the database doesn't exist
    SELECT 0 AS TestResult;
END

For backward compatibility with SQL Server 2000, replace sys.tables with sys.sysobjects:

SELECT 1 AS TestResult FROM [@DatabaseName].sys.sysobjects WHERE id = 1;

Here, your tool can check for a 1 in the result to return DB - Ok, or handle errors/0 as DB - fail.

3. Key considerations #

• Permissions: Ensure your monitoring service account has at least VIEW ANY DATABASE permission on the instance (to query sys.databases) and CONNECT permission on any target user databases.
• Performance: Keep queries simple—avoid complex joins or large result sets, since monitoring checks run frequently and shouldn't consume excessive resources.
• Compatibility: If you need to support SQL Server 2000, stick with sys.sysdatabases instead of sys.databases.

内容的提问来源于stack exchange，提问作者Posix