開發與維護資料庫時,常需快速掌握資料庫物件結構。本文整理 T-SQL 查詢系統目錄檢視與資訊結構描述的常用腳本,涵蓋資料庫、檔案、資料表、欄位、檢視表、結構描述等核心物件。
SELECT name AS 資料庫名, database_id AS 資料庫ID
FROM sys.databases;
EXEC sp_helpdb;
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');
USE mydatabase;
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES;
USE mydatabase;
GO
SELECT
COLUMN_NAME AS 欄位名,
DATA_TYPE AS 資料類型,
ISNULL(CHARACTER_MAXIMUM_LENGTH, '') AS 長度,
ISNULL(COLUMN_DEFAULT, '') AS 預設值
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'student';
USE mydatabase;
GO
DECLARE @FieldNum INT;
SET @FieldNum = (
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'student'
);
PRINT N'表 student 中欄位個數:' + CAST(@FieldNum AS VARCHAR(10));
USE mydatabase;
GO
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS;
USE mydatabase;
GO
SELECT * FROM INFORMATION_SCHEMA.VIEWS;
USE mydatabase;
GO
SELECT
SCHEMA_NAME AS 結構描述名,
SCHEMA_OWNER AS 擁有者
FROM INFORMATION_SCHEMA.SCHEMATA;
善用 INFORMATION_SCHEMA 標準檢視與 sys.* 系統目錄,可快速掌握資料庫架構。建議將常用查詢存為程式碼片段,提升日常開發維護效率。