SQL Database Schema To Excel

   I searched lot of web site for document preparation for Database design
in excel format, at last I developed one web application for generating
SQL DB Schema to excel format , use the following code In page load
event of aspx page
protected void Page_Load(object sender, EventArgs e)
 System.Data.SqlClient.SqlConnection con =
 new System.Data.SqlClient.SqlConnection
 //System.Data.DataTable tbl = con.GetSchema("Databases");

 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("<tr><td colspan='6' align='center'> </td></tr>");
 System.Data.DataTable tblTables = null;

 tblTables = con.GetSchema(System.Data.SqlClient.
 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 </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>");
 sb.Append("<tr><td colspan=6></td></tr>");

 sb.Append(" </TABLE>");

 Response.ContentType = "application/vnd.ms-excel";
 Response.AddHeader("Content-Disposition", "attachment; filename=Reports.xls");

The out put will be displayed like as follows :


Tags: , , , , , , ,

6 Responses to “SQL Database Schema To Excel”

  1. ZAREMA Says:

    Thanks the author for article. The main thing do not forget about users, and continue in the same spirit. http://odessacity.net/

  2. gualetar Says:

    The subject is fully clear but why does the text lack clarity? But in general your blog is great.

  3. Exporting SQL Server schema to Excel « Jgkim's Blog Says:

    […] https://nissankg.wordpress.com/2010/03/03/sql-database-schema-to-excel/ […]

  4. Phantom1024 Says:

    great script. I had it running in seconds!

  5. Anas Says:

    very nice program Thanks

  6. Niraj Says:

    Thanks for this nice post, but can explain how can I export views in the similar format.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: