SQL Server 2000 實用技巧大全：系統表查詢、分頁、交易與進階技巧

一、系統表快速查詢

1. 查表名

-- 模糊查表名
SELECT * FROM sysobjects WHERE name LIKE '%user%';

-- 知欄位名反查表名
SELECT * FROM sysobjects s
WHERE EXISTS (
    SELECT 1 FROM syscolumns WHERE id = s.id AND name LIKE '%user%'
);

2. 常用清單查詢

-- 所有使用者表
SELECT name FROM sysobjects WHERE xtype = 'U';

-- 指定表所有欄位
SELECT name FROM syscolumns WHERE id = OBJECT_ID('表名');

-- 所有視圖/資料表/欄位資訊
SELECT * FROM information_schema.tables;
SELECT * FROM information_schema.views;
SELECT * FROM information_schema.columns;

3. 完整表結構查詢（含主鍵、身分識別、擴充屬性說明）

DECLARE @table_name VARCHAR(MAX) = '你的表名';
SELECT
    sys.columns.name AS 欄位名,
    sys.types.name AS 資料型別,
    sys.columns.max_length AS 長度,
    sys.columns.is_nullable AS 允許NULL,
    (SELECT COUNT(*) FROM sys.identity_columns
     WHERE object_id = sys.columns.object_id AND column_id = sys.columns.column_id) AS 是否身分識別,
    (SELECT value FROM sys.extended_properties
     WHERE major_id = sys.columns.object_id AND minor_id = sys.columns.column_id) AS 說明
FROM sys.columns, sys.tables, sys.types
WHERE sys.columns.object_id = sys.tables.object_id
  AND sys.columns.system_type_id = sys.types.system_type_id
  AND sys.tables.name = @table_name
ORDER BY sys.columns.column_id;

二、分頁與排序技巧

1. 筆畫排序

SELECT * FROM TableName
ORDER BY CustomerName COLLATE Chinese_PRC_Stroke_CI_AS;

2. 通用分頁 (SQL 2005+)

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY tab.ID DESC) AS rownum, tab.*
    FROM 表名 AS tab
) AS t
WHERE rownum BETWEEN @start AND @end;

三、交易控制範本

BEGIN TRAN;
    INSERT INTO TableName VALUES (...);
    -- 若發現錯誤
    ROLLBACK TRAN;
    -- 若正常
    COMMIT TRAN;

四、全文匹配查詢（避免 LIKE '%keyword%' 全掃描）

WHERE 欄位 LIKE N'%[^a-zA-Z0-9]China[^a-zA-Z0-9]%'
   OR 欄位 LIKE N'%[^a-zA-Z0-9]China'
   OR 欄位 LIKE N'China[^a-zA-Z0-9]%'
   OR 欄位 = N'China';

五、執行時間統計

DECLARE @d DATETIME = GETDATE();
SELECT * FROM SYS_ColumnProperties;
SELECT DATEDIFF(MS, @d, GETDATE()) AS 執行毫秒數;

六、集合運算子速查

七、Access 系統表查詢

-- 顯示所有使用者表 (需先在選項勾選「系統物件」)
SELECT name FROM MSysObjects WHERE type = 1 AND flags = 0;