Exporting SQL Server schema to Excel

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/566ec9b7-35f1-4e0d-93ac-ed5868d8f521

http://nissankg.wordpress.com/2010/03/03/sql-database-schema-to-excel/

조금 수정했습니다.

protected void Page_Load(object sender, EventArgs e)
{
System.Text.StringBuilder m_sb = new System.Text.StringBuilder();

DbTableToExcel(“Db1”, ref m_sb);
DbTableToExcel(“Db2”, ref m_sb);

Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = “application/vnd.ms-excel”;
string ExcelFile = “attachment; filename=DbSchema.xls”;
Response.AddHeader(“Content-Disposition”, ExcelFile);
Response.Write(m_sb);
Response.End();
}

void DbTableToExcel(string DbName, ref System.Text.StringBuilder sb)
{
string strCon = “Data Source=192.168.1.1;Initial Catalog=” + DbName + “;User ID=sa;Password=1234;”;
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon);
con.Open();
//System.Data.DataTable tbl = con.GetSchema(“Databases”);
//ExportExcel(tbl);

//System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(” <TABLE BORDER=1>”);
sb.Append(“<tr><td colspan=’6′ align=’center’>”);
sb.Append(“<H3>Table Design for: -” + con.Database.ToString() + “</H3>”);

sb.Append(“</td></tr>”);

sb.Append(“<tr><td colspan=’6′ align=’center’> </td></tr>”);
con.Close();
System.Data.DataTable tblTables = null;

con.Open();
tblTables = con.GetSchema(System.Data.SqlClient.
SqlClientMetaDataCollectionNames.Tables,
new string[] { null, null, null, “BASE TABLE” });
DataTable columnsTable = null;
foreach (System.Data.DataRow rowDatabase in tblTables.Rows)
{
columnsTable = con.GetSchema(SqlClientMetaDataCollectionNames.Columns, new string[] { null, null, rowDatabase[“TABLE_NAME”].ToString() });
sb.Append(“<tr><td colspan=’5′ align=’center’>”);
sb.Append(“<b>Table Description</b></td>”);
sb.Append(“<td align=’center’>”);
sb.Append(“<b>Table Name</b></td></tr>”);
sb.Append(“<tr><td colspan=’5′ align=’center’ >” + rowDatabase[“TABLE_NAME”].ToString() + “</td><td colspan=’1′ align=’center’>” + rowDatabase[“TABLE_NAME”].ToString() + “</td></tr>”);
sb.Append(“<tr><td><b> SL No</b></td> <td><b> Field Name</b></td> <td><b> Data Type</b></td> <td><b> Size</b></td> <td><b> Constraint </b></td><td><b> Explanation(Korean)</b></td><td><b> Explanation(English)</b></td><td><b> Explanation(Chinese)</b></td></tr>”);
int i = 1;
foreach (System.Data.DataRow columnsRows in columnsTable.Rows)
{

sb.Append(“<tr><td>” + i + “</td> <td>” + columnsRows[“COLUMN_NAME”].ToString() + “</td> <td>” + columnsRows[“DATA_TYPE”].ToString() + “</td> <td>” + columnsRows[“CHARACTER_MAXIMUM_LENGTH”].ToString() + “</td> <td></td><td></td></tr>”);
i++;
}
sb.Append(“<tr><td colspan=6></td></tr>”);
}

con.Close();
sb.Append(” </TABLE>”);
}
}

Advertisements

답글 남기기

아래 항목을 채우거나 오른쪽 아이콘 중 하나를 클릭하여 로그 인 하세요:

WordPress.com 로고

WordPress.com의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

Twitter 사진

Twitter의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

Facebook 사진

Facebook의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

Google+ photo

Google+의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

%s에 연결하는 중

%d 블로거가 이것을 좋아합니다: