.NET、ASP 中得到 SQL Server 2005/2008 字段描述信息

作者：孟宪会阅读：1358 发表于：2010-09-17 14:36:13

C# 代码

String TableName = "TestTable";
SqlConnection cn = new SqlConnection();
cn.ConnectionString = "Data Source=192.168.3.1\\SQL2005;Initial Catalog=TestDB;User Id=abc;Password=abc;";
cn.Open();
SqlCommand cmd = new SqlCommand();
String sql = "SELECT c.name AS [字段名字],ISNULL(ex.value,'无描述') AS [描述] ";
sql += "FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex ";
sql += "ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description'";
sql += "WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 AND OBJECT_NAME(c.object_id) = '" + TableName + "'";
cmd.CommandText = sql;
cmd.Connection = cn;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Response.Write("<li>字段名 = " + dr[0].ToString() + " 描述 = " + dr[1].ToString());
}
cn.Close();

VBScript 代码

<%
Dim conn
Dim TableName, SQL
'表的名字
TableName = "TestTable"
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=SQLNCLI;Server=(local)\SQL2005;Database=TestDB;Uid=abc;Pwd=abc;"
conn.Open
SQL = "SELECT c.name AS ColumnName,ex.value AS [Description] " & _
                "FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex " & _
                "ON ex.major_id = c.object_id AND ex.minor_id = c.column_id  AND ex.name = 'MS_Description' " & _
                "WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 AND OBJECT_NAME(c.object_id) = '" & TableName & "' " & _
                "Order By c.object_id"
Set rs = conn.Execute(SQL)
While Not rs.EOF
    Response.Write "<li>字段名字：" & rs("ColumnName") & " 描述：" & rs("Description")
  rs.MoveNext
Wend
conn.Close
%>

【发表评论】【打印文章】【收藏本页】