Sub ConvertNtextToNvarchar()
    Dim conn As Object
    Dim rs As Object
    Dim strConn As String
    Dim strSQL As String
    Dim strTable As String
    Dim strColumn As String
    Dim strNullable As String
    Dim strAlterSQL As String
    Dim iCount As Integer
    Dim iSuccess As Integer
    Dim response As VbMsgBoxResult
    
    ' 连接字符串（根据实际 SQL Server 实例调整 Data Source）
    strConn = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=jiaowu;User ID=sa;Password=123456;"
    
    ' 查找所有 ntext 字段
    strSQL = "SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE " & _
             "FROM INFORMATION_SCHEMA.COLUMNS " & _
             "WHERE DATA_TYPE = 'ntext' " & _
             "ORDER BY TABLE_NAME, ORDINAL_POSITION"
    
    On Error GoTo ErrHandler
    
    ' 建立连接
    Set conn = CreateObject("ADODB.Connection")
    conn.Open strConn
    
    ' 获取记录集
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSQL, conn
    
    If rs.EOF Then
        Debug.Print "未找到任何 ntext 类型的字段，无需转换。"
        rs.Close
        conn.Close
        Set rs = Nothing
        Set conn = Nothing
        Exit Sub
    End If
    
    ' 列出所有待修改字段
    Debug.Print "以下字段将被修改为 NVARCHAR(3000)："
    Do Until rs.EOF
        Debug.Print rs("TABLE_NAME") & "." & rs("COLUMN_NAME")
        rs.MoveNext
    Loop
    
    ' 用户确认
    response = MsgBox("确认要将以上所有 ntext 字段转换为 NVARCHAR(3000) 吗？" & vbCrLf & _
                      "此操作不可逆，请提前备份数据！", vbYesNo + vbExclamation, "危险操作确认")
    If response = vbNo Then
        Debug.Print "操作已取消。"
        rs.Close
        conn.Close
        Set rs = Nothing
        Set conn = Nothing
        Exit Sub
    End If
    
    ' 回到记录集开头
    rs.MoveFirst
    
    iCount = 0
    iSuccess = 0
    
    ' 逐条执行 ALTER TABLE
    Do Until rs.EOF
        strTable = rs("TABLE_NAME")
        strColumn = rs("COLUMN_NAME")
        strNullable = rs("IS_NULLABLE")          ' YES 或 NO
        iCount = iCount + 1
        
        ' 构建 ALTER 语句，保留原有 NULL/NOT NULL 约束
        strAlterSQL = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strColumn & "] NVARCHAR(3000) "
        If strNullable = "YES" Then
            strAlterSQL = strAlterSQL & "NULL"
        Else
            strAlterSQL = strAlterSQL & "NOT NULL"
        End If
        On Error Resume Next
        conn.Execute strAlterSQL ' 执行修改
        If Err.Number = 0 Then
            iSuccess = iSuccess + 1
            Debug.Print "成功: " & strTable & "." & strColumn
        Else
            Debug.Print "失败: " & strTable & "." & strColumn & " - " & Err.Description
            Err.Clear
        End If
        On Error GoTo ErrHandler
        rs.MoveNext
    Loop
    Debug.Print "转换完成。共 " & iCount & " 个字段，成功 " _
              & iSuccess & " 个，失败 " & (iCount - iSuccess) & " 个。"
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    Exit Sub
ErrHandler:
    Debug.Print "发生致命错误: " & Err.Description
    If Not rs Is Nothing Then
        If rs.State = 1 Then rs.Close
    End If
    If Not conn Is Nothing Then
        If conn.State = 1 Then conn.Close
    End If
    Set rs = Nothing
    Set conn = Nothing
End Sub

