在開發通用後台、代碼生成器、動態報表時,常需運行時獲取資料庫結構信息。本文展示 ASP 通過 ADO Schema 與 Recordset 獲取表、欄位元數據,並演示動態 DDL 操作。
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB;Data Source=IP;Initial Catalog=DBName;UID=user;PWD=pwd"
%>
<%
Set rs = Conn.OpenSchema(20) ' adSchemaTables = 20
Do While Not rs.EOF
If rs("TABLE_TYPE") = "BASE TABLE" Then ' 過濾系統表/視圖
Response.Write "資料庫:" & rs("TABLE_CATALOG") & "<br>"
Response.Write "擁有者:" & rs("TABLE_SCHEMA") & "<br>"
Response.Write "表名:" & rs("TABLE_NAME") & "<br><br>"
End If
rs.MoveNext
Loop
%>
<%
Dim i, j, Sql
Set rs = Server.CreateObject("ADODB.Recordset")
' WHERE 1<>1 確保不返回數據行,僅獲取結構
Sql = "SELECT * FROM [Things] WHERE 1<>1"
rs.Open Sql, Conn, 1, 1 ' adOpenKeyset, adLockReadOnly
j = rs.Fields.Count
For i = 0 To j - 1
Response.Write "第 " & (i+1) & " 個欄位:" & rs.Fields(i).Name & "<br>"
Next
%>
同樣透過 Conn.Execute 執行 T-SQL DDL 語句:
Sql = "ALTER TABLE [Things] DROP COLUMN thingsType"
Conn.Execute Sql
Sql = "ALTER TABLE [Things] ADD thingsColor VARCHAR(20) DEFAULT 'Red'"
Conn.Execute Sql
' 建表
Sql = "CREATE TABLE Logs (Id INT IDENTITY, Msg VARCHAR(200), CreateTime DATETIME DEFAULT GETDATE())"
Conn.Execute Sql
' 刪表(慎用!)
Sql = "DROP TABLE OldTable"
Conn.Execute Sql
執行 DDL 需
db_ddladmin或db_owner權限。生產環境建議透過預存程序或遷移腳本管理結構變更,避免直接在頁面執行。
掌握 Schema 查詢與動態 DDL,讓你的 ASP 應用具備「自描述」與「自演化」能力。