ASP 快速檢視 SQL Server 資料表結構腳本：欄位名、型別、長度、可空性一覽

腳本用途

在無管理工具環境下，透過 ASP 頁面快速檢視指定資料庫所有使用者資料表的欄位定義，包含名稱、資料型別、長度、身分識別、可空性等關鍵屬性。

完整 ASP 程式碼

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "ODBC名稱", "使用者", "密碼"

' 僅抓取使用者資料表 (type='U')
Sql1 = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name"
Set rsTables = Server.CreateObject("ADODB.Recordset")
rsTables.Open Sql1, conn, 1, 2

Do While Not rsTables.EOF
    tableName = rsTables("name")
    Response.Write "
表名：" & tableName & "
"

    ' 開啟資料表取得欄位結構描述
    Set rsCols = Server.CreateObject("ADODB.Recordset")
    rsCols.Open "SELECT * FROM " & tableName, conn, 1, 2

    For i = 0 To rsCols.Fields.Count - 1
        Set fld = rsCols.Fields(i)
        Response.Write "欄位名：" & fld.Name & "
型別："

        ' 型別對照 (ADO DataTypeEnum 常見值)
        Select Case fld.Type
            Case 3:   Response.Write "int"
            Case 129: Response.Write "char"
            Case 201: Response.Write "text"
            Case 202: Response.Write "nvarchar"
            Case 131: Response.Write "numeric"
            Case 2:   Response.Write "smallint"
            Case 11:  Response.Write "bit"
            Case 135: Response.Write "smalldatetime"
            Case 203: Response.Write "ntext"
            Case 6:   Response.Write "money"
            Case 130: Response.Write "nchar"
            Case 4:   Response.Write "real"
            Case 5:   Response.Write "float"
            Case 128: Response.Write "binary"
            Case Else: Response.Write "未知(" & fld.Type & ")"
        End Select

        If fld.DefinedSize > 0 Then Response.Write " 長度：" & fld.DefinedSize

        ' Attributes 位元組合判斷可空性 (常見組合)
        Select Case fld.Attributes
            Case 16:  Response.Write " 自動編號"
            Case 24:  Response.Write " 不允許空值"
            Case 120: Response.Write " 允許空值"
            Case 8:   Response.Write " 不允許空值"
            Case 104: Response.Write " 允許空值"
            Case 234: Response.Write " 允許空值"
            Case 138: Response.Write " 不允許空值"
        End Select

        Response.Write "
屬性值：" & fld.Attributes & "
----------------
"
    Next
    rsCols.Close
    rsTables.MoveNext
Loop

rsTables.Close
conn.Close
Set rsTables = Nothing
Set conn = Nothing
%>

關鍵技術點

擴充建議

• 加入索引、外鍵、預設值查詢 (查詢 sysindexes、syscolumns、syscomments)
• 輸出為 HTML 表格或 JSON 供前端元件呈現
• 支援多資料庫切換下拉選單
• 加入「複製建表腳本」功能 (產生 CREATE TABLE DDL)

適用環境

IIS + ASP (Classic ASP) + SQL Server 7.0/2000/2005+，需啟用 ADO 元件 (MDAC)。