A One Page Reference for Everyday SQL Server Work

A useful SQL Server reference helps you recall the next small command when the question is already clear. Keep the core queries compact, and keep their assumptions close enough to read before execution.

Which Server and Database Am I Using

Put connection context at the top of the reference. Similar server names and multiple query tabs make assumptions easy. Check the actual connection before interpreting output or preparing a change.

SELECT @@SERVERNAME AS server_name, DB_NAME() AS database_name,
       ORIGINAL_LOGIN() AS original_login,
       SERVERPROPERTY('ProductVersion') AS product_version;
SELECT compatibility_level
FROM sys.databases WHERE database_id = DB_ID();

The original login and current execution identity can differ under impersonation. Add SUSER_SNAME and USER_NAME when that distinction matters. A context check should answer the specific identity question being investigated.

Keep a small heading above every saved result with the observation time. A correct query from the wrong instance is still the wrong evidence. Context is especially important when comparing two environments.

What Is the Object and Its Definition

SELECT SCHEMA_NAME(schema_id) AS schema_name,
       name, type_desc, modify_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY schema_name, name;
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.YourProcedure'))
       AS module_definition;

Replace the procedure name with the intended object in the current database. Always include its schema when resolving names. OBJECT_DEFINITION can return NULL because of visibility, encryption, or an unsuitable or missing object.

Do not interpret every NULL as proof that the procedure is absent. Check object type and permissions first. Catalog views describe metadata, while dynamic management views describe current or accumulated engine activity.

What Is Running and Waiting

SELECT session_id, status, command, blocking_session_id,
       wait_type, wait_time, total_elapsed_time
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID
ORDER BY total_elapsed_time DESC;

This query is a starting point for active requests, not a complete incident report. Required permissions depend on the SQL Server version. Restricted visibility can hide sessions you expected to see.

A wait describes what a request is waiting for at that moment. It does not automatically identify the root cause. Follow blocking relationships and inspect representative plans or resource evidence as the question requires.

How Much Space Is Inside the Data Files

SELECT name, type_desc,
       size * 8.0 / 1024 AS file_size_mb,
       FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024 AS used_mb
FROM sys.database_files
WHERE type = 0;

Run this in the database being examined. The query focuses on ROWS data files and their allocated usage. It does not report free capacity on the underlying Windows volume.

Keep file capacity and disk capacity as separate questions. A file can contain reusable space while its volume is nearly full, or the reverse. Log-space analysis also needs its own appropriate view.

What Transaction State Am I In

SELECT @@TRANCOUNT AS transaction_count,
       XACT_STATE() AS transaction_state;
DBCC USEROPTIONS WITH NO_INFOMSGS;

These checks describe the current session. XACT_STATE distinguishes no transaction, a committable transaction, and an uncommittable transaction. An open transaction deserves an ownership check before you commit or roll back anything.

DBCC USEROPTIONS helps expose session settings that can affect behavior. Compare the application's connection when SSMS behaves differently. Running the check only in your own query window cannot explain every application session.

Avoid putting an unconditional rollback at the bottom of a general reference. It could undo work the caller expected to keep. State the decision separately from the diagnostic command.

How Do I Inspect a Known Table Quickly

-- Replace the schema-qualified name before running.
EXEC sys.sp_help @objname = N'dbo.YourTable';

This provides a convenient object summary when the name and permissions are appropriate. Use catalog views when you need a filtered, repeatable inventory across many objects. A one-object inspection and a server-wide audit are different tasks.

Keep the six query blocks on the compact reference page and retain these explanations as companion notes. One page should remain a retrieval aid rather than a compressed textbook. Add commands only when repeated use earns their place.

Review the reference after version changes and remove commands you no longer understand or use. Mark placeholders clearly and keep modification scripts elsewhere. The best reference reduces hesitation without encouraging blind execution.

A quick reference is not permission to run everything, it is a reminder of the right question and command.

This post was rewritten from scratch in September 2026. The original, published on 2008-06-25, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.