您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

T-SQL 入門必學：查詢資料庫物件的系統檢視與指令大全

編輯：關於SqlServer

前言

開發與 DBA 日常最常見的動作莫過於「這張表有哪些欄位？」、「那個 View 定義是什麼？」。SQL Server 提供豐富的系統目錄檢視與系統程序，本文整理最實用的查詢模板，助你秒級定位物件中繼資料。

一、資料庫層級查詢

1.1 列出所有使用者資料庫

-- 方法一：系統檢視（推薦）
SELECT name AS DatabaseName, database_id AS DB_ID
FROM sys.databases
WHERE database_id > 4;  -- 排除 master, tempdb, model, msdb

-- 方法二：系統程序
EXEC sp_helpdb;

1.2 檢視資料庫檔案結構

-- 當前資料庫檔案
SELECT type_desc, name, physical_name, size, max_size, growth
FROM sys.database_files;

-- 指定資料庫檔案（跨資料庫查詢）
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('MyDatabase');

二、資料表與結構描述查詢

2.1 列出目前資料庫所有使用者資料表

USE MyDatabase;
GO
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
-- 或
SELECT name FROM sys.objects WHERE type = 'U' AND is_ms_shipped = 0;

2.2 取得指定資料表完整欄位定義

USE MyDatabase;
GO
SELECT 
    COLUMN_NAME      AS 欄位名,
    DATA_TYPE        AS 資料型別,
    CHARACTER_MAXIMUM_LENGTH AS 長度,
    COLUMN_DEFAULT   AS 預設值,
    IS_NULLABLE      AS 允許NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'student'
ORDER BY ORDINAL_POSITION;

2.3 進階：含身分識別、主鍵、擴充屬性的完整結構


SELECT 
    c.name AS 欄位名,
    t.name AS 型別,
    c.max_length AS 長度,
    c.is_nullable AS 允許NULL,
    CASE WHEN ic.object_id IS NOT NULL THEN 1 ELSE 0 END AS 是身分識別,
    CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS 是主鍵,
    ep.value AS 說明
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN sys.identity_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN (
    SELECT ic.column_id, ic.object_id
    FROM sys.indexes i
    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    WHERE i.is_primary_key = 1
) pk ON c.object_id = pk.object_id AND c.column_id = pk.column_id
LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.class = 1
WHERE c.object_id = OBJECT_ID('dbo.student')
ORDER BY c.column_id;

2.4 統計資料表欄位數


DECLARE @FieldNum INT;
SELECT @FieldNum = COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'student';
PRINT N'表 student 共有 ' + CAST(@FieldNum AS VARCHAR(10)) + N' 個欄位';

三、檢視、結構描述與其他物件

3.1 列出所有 View


SELECT * FROM INFORMATION_SCHEMA.VIEWS;
-- 或
SELECT name, OBJECT_DEFINITION(object_id) AS Definition FROM sys.views;

3.2 列出所有 Schema 及擁有者


SELECT schema_name AS 結構描述名, schema_owner AS 擁有者
FROM INFORMATION_SCHEMA.SCHEMATA;

3.3 查詢所有儲存程序


SELECT name FROM sys.objects WHERE type = 'P' AND is_ms_shipped = 0;
-- 含定義
SELECT name, OBJECT_DEFINITION(object_id) AS Definition FROM sys.objects WHERE type = 'P';

3.4 查詢資料表索引與約束


EXEC sp_helpindex 'student';      -- 索引
EXEC sp_helpconstraint 'student'; -- 約束（PK, FK, CK, DF）

四、實用小技巧

快速取得物件定義：OBJECT_DEFINITION(OBJECT_ID('proc_name')) 比 sp_helptext 更適合程式碼處理。
判斷物件是否存在：IF OBJECT_ID('dbo.student', 'U') IS NOT NULL ...（U=Table, P=Proc, V=View, FN=Scalar Function）。
跨資料庫查詢：善用 sys.master_files、sys.databases 避免切換資料庫內容。