SQL Server 系統表深度利用：遍歷所有庫獲取視圖、表、存儲過程、列信息

核心思路

利用 master 系統數據庫中的元數據視圖，結合動態 SQL 跨數據庫批量收集對象信息：數據庫列表 → 每庫的表/視圖/存儲過程 → 表的列詳情。

準備工作：啟用 xp_cmdshell (可選，用於獲取實例名)

-- 啟用高級選項與 xp_cmdshell
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

-- 獲取可用實例名 (需網絡連通)
EXEC sys.xp_cmdshell 'sqlcmd -Lc';

第一層：獲取所有用戶數據庫

-- 方法 1：舊版兼容
SELECT name FROM master..sysdatabases 
WHERE name NOT IN ('master','tempdb','model','msdb')  -- 排除系統庫
ORDER BY name;

-- 方法 2：現代視圖 (推薦)
SELECT name FROM sys.databases 
WHERE database_id > 4  -- 過濾系統庫
  AND state_desc = 'ONLINE'
ORDER BY name;

第二層：獲取單庫內的對象列表

USE YourDatabase;  -- 切換上下文

-- 1. 所有用戶表
SELECT name, id, crdate FROM sysobjects WHERE xtype = 'U' ORDER BY name;

-- 2. 所有視圖
SELECT name, id, crdate FROM sysobjects WHERE xtype = 'V' ORDER BY name;

-- 3. 所有存儲過程
SELECT name, id, crdate FROM sysobjects WHERE xtype = 'P' ORDER BY name;

-- 4. 所有觸發器
SELECT name, id, crdate FROM sysobjects WHERE xtype = 'TR' ORDER BY name;

第三層：獲取表的詳細列信息 (含主鍵、標識、說明)

SELECT 
    表名       = CASE WHEN a.colorder = 1 THEN d.name ELSE '' END,
    表說明     = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '') ELSE '' END,
    字段序號   = a.colorder,
    字段名     = a.name,
    標識       = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END,
    主鍵       = CASE WHEN EXISTS (
                        SELECT 1 FROM sysobjects 
                        WHERE xtype = 'PK' 
                          AND name IN (
                              SELECT name FROM sysindexes 
                              WHERE indid IN (
                                  SELECT indid FROM sysindexkeys 
                                  WHERE id = a.id AND colid = a.colid
                              )
                          )
                      ) THEN '√' ELSE '' END,
    類型       = b.name,
    占用字節   = a.length,
    長度       = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
    小數位     = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),
    允許空     = CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END,
    默認值     = ISNULL(e.text, ''),
    字段說明   = ISNULL(g.[value], '')
FROM syscolumns a
LEFT JOIN systypes b ON a.xusertype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id
LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
WHERE a.id = OBJECT_ID('YourTableName')
ORDER BY a.id, a.colorder;

現代化標準視圖寫法 (推薦，跨版本穩定)

-- 所有數據庫
SELECT name FROM sys.databases ORDER BY name;

-- 所有表 (指定庫上下文)
SELECT * FROM information_schema.tables WHERE table_type = 'BASE TABLE';

-- 所有視圖
SELECT * FROM information_schema.views;

-- 所有存儲過程
SELECT * FROM information_schema.routines WHERE routine_type = 'PROCEDURE';

-- 指定表的列詳情
SELECT 
    COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE,
    COLUMN_DEFAULT, ORDINAL_POSITION
FROM information_schema.columns 
WHERE table_name = 'YourTableName'
ORDER BY ORDINAL_POSITION;

進階：動態跨庫收集所有表結構 (生成文檔用)

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
USE ' + QUOTENAME(name) + N';
SELECT 
    DB_NAME() AS DatabaseName,
    t.TABLE_NAME,
    c.COLUMN_NAME,
    c.DATA_TYPE,
    c.CHARACTER_MAXIMUM_LENGTH,
    c.IS_NULLABLE,
    c.COLUMN_DEFAULT,
    ep.value AS ColumnDescription
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN sys.extended_properties ep 
    ON ep.major_id = OBJECT_ID(t.TABLE_NAME)
   AND ep.minor_id = c.ORDINAL_POSITION
   AND ep.class = 1
WHERE t.TABLE_TYPE = ''BASE TABLE''
ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;'
FROM sys.databases
WHERE database_id > 4 AND state_desc = 'ONLINE';

-- 執行或輸出 @sql
-- EXEC sp_executesql @sql;
PRINT LEN(@sql);  -- 檢查長度

sysobjects 關鍵列速查

常用 COLUMNPROPERTY 函數屬性

• IsIdentity：是否標識列 (1=是)
• PRECISION：數值精度/字符最大長度
• Scale：小數位數
• IsComputed：是否計算列
• IsRowGuidCol：是否 ROWGUIDCOL