實用 SQL Server 查詢腳本集錦：結構、空間、效能、維運

前言

整理自實務專案，涵蓋結構描述、空間分析、效能診斷、維運管理四大類常用 T-SQL 腳本，持續更新中。

1. 結構描述類

1.1 列出所有資料表完整結構（含身分識別、主鍵、索引、擴充屬性）

SELECT
  CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
  a.colorder AS 字段序號,
  a.name AS 字段名,
  CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 標識,
  CASE WHEN EXISTS (
    SELECT 1 FROM sysindexes si
    JOIN sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
    WHERE si.id = a.id AND sik.colid = a.colid
      AND (si.status & 0x800) = 0x800  -- 主鍵
  ) THEN 'PK' ELSE '' END AS 主鍵,
  t.name AS 資料類型,
  a.length AS 長度,
  CASE WHEN a.isnullable = 1 THEN 'Y' ELSE 'N' END AS 允許空值,
  ISNULL(g.[value], '') AS 擴充說明
FROM syscolumns a
JOIN systypes t ON a.xtype = t.xusertype
JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U'
LEFT JOIN sys.extended_properties g ON g.major_id = a.id AND g.minor_id = a.colorder
WHERE d.name <> 'dtproperties'
ORDER BY d.name, a.colorder;

1.2 檢視所有外鍵關聯

SELECT
  OBJECT_NAME(f.parent_object_id) AS 子表,
  COL_NAME(fc.parent_object_id, fc.parent_column_id) AS 子欄位,
  OBJECT_NAME(f.referenced_object_id) AS 父表,
  COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS 父欄位,
  f.name AS 外鍵名稱
FROM sys.foreign_keys f
JOIN sys.foreign_key_columns fc ON f.object_id = fc.constraint_object_id
ORDER BY 子表, 父表;

2. 空間與容量分析

2.1 各資料表資料列數與空間佔用

SELECT
  t.name AS 表名,
  p.rows AS 資料列數,
  SUM(a.total_pages) * 8 / 1024.0 AS 總空間_MB,
  SUM(a.used_pages) * 8 / 1024.0 AS 已用空間_MB,
  SUM(a.data_pages) * 8 / 1024.0 AS 資料空間_MB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0 AND i.index_id IN (0,1)
GROUP BY t.name, p.rows
ORDER BY 總空間_MB DESC;

2.2 資料庫檔案大小與可用空間

SELECT
  name AS 邏輯檔名,
  physical_name AS 實體路徑,
  size * 8 / 1024.0 AS 目前大小_MB,
  FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024.0 AS 已用_MB,
  (size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024.0 AS 可用_MB
FROM sys.database_files;

3. 效能診斷

3.1 找出最耗資源的查詢（依總 CPU、讀取、執行次數）

SELECT TOP 20
  qs.total_worker_time / qs.execution_count AS 平均_CPU_ms,
  qs.total_logical_reads / qs.execution_count AS 平均邏輯讀取,
  qs.execution_count AS 執行次數,
  SUBSTRING(qt.text, qs.statement_start_offset/2 + 1,
    (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS SQL片段,
  qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

3.2 檢視缺失索引建議

SELECT
  migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS 改善指數,
  mid.statement AS 物件,
  mid.equality_columns, mid.inequality_columns, mid.included_columns,
  'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(mid.statement, '[', ''), ']', ''), '.', '_')
    + ' ON ' + mid.statement
    + ' (' + ISNULL(mid.equality_columns, '') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END + ISNULL(mid.inequality_columns, '') + ')'
    + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS 建議建立腳本
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY 改善指數 DESC;

4. 維運管理

4.1 殺掉特定資料庫所有連線（切換單一使用者模式前）

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += 'KILL ' + CAST(spid AS NVARCHAR) + '; '
FROM sys.sysprocesses
WHERE dbid = DB_ID('YourDB') AND spid <> @@SPID;
EXEC (@sql);

4.2 重建所有索引（線上、可中斷）

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += 'ALTER INDEX ALL ON ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ' REBUILD WITH (ONLINE = ON, MAXDOP = 2); '
FROM sys.tables
WHERE is_ms_shipped = 0;
EXEC (@sql);

4.3 更新所有資料表統計資料

EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN';

使用建議

• 將常用腳本存為 `.sql` 檔，配合 `sqlcmd` 或 Agent 作業排程自動化。
• 效能類查詢需具備 `VIEW SERVER STATE` 權限。
• 生產環境執行 DDL（如重建索引）請先評估維護視窗與鎖定影響。