Printing SQL Server Database Schema

by

I’ve been working with SQL Server 2000 for about 2 months now, and it completely frustrates me that I cannot print table schema from Microsoft’s Enterprise Manager. I’m one of those people who like to have printouts of the table’s I’m working with as it makes it easier for me to generate my SQL statements.

Doing some searching on Google, I found a couple of tools that will print schema, but they wanted at least $59US! So I decided to write my own script and offer it up for free.

The script uses ASP classic, so it will need to run on an IIS server. You will need to modify the script and specify data source/ip address of your SQL Server, choose an Initial Catalog/Database”, and supply a *username and password. When you first run the script in your web browser, you will see a navigation bar along the left side of the page that lists the tables for your selected database. When you click on a table name, the schema for that table will appear. The action of clicking on a table name invokes an xmlhttprequest object to display the table schema. This means that the table list is not being regenerated with each request. It also means that you can click on multiple tables to stack them up! To remove a table from the display, simple click on the table name and it is removed from the DOM.

The Hide Table List link that appears above the list of tables is simply there to hide the table navigation bar when you want to print your table schema. Once you hide the list, you must refresh the browser to bring it back.

So, here is the file. Help yourself and I look forward to any feedback!

43 thoughts on “Printing SQL Server Database Schema”

  2. You need to learn how to do ERDs, short for entity relationship diagrams, which has all info you want.

    Primary Key’s, Foreign Key Relationships.

    Using MS Visio or a number of other tools, to reverse engineer your database structure into a very printable diagram. Depending on how big a printout you want. I usually want a huge colored one, right above my desk..

    There may be freeware or shareware ones out there…But I tend to use Visio the most myself. Because it will draw out the table structures itself. Just make sure to create the relationships that you want to show up in your diagram in the tables, and then it will display that as well..

    Good luck!

  3. Good work! One minor fix: line 180 of index.asp should be:
    sOut += ';

    Also, I’m unable to get more than 1 page to print from Firefox 1.0.1. And IE 6.0 messes up the formatting of “Table Schema for…” headings.

  4. My suggested code fix above was broken during the parse. Line 180 contains an opening unordered list tag, but it should be a closing unordered list tag.

  5. I had always just copied all the rows in Design view and pasted into Excel. Resize a couple of columns and print away.

  7. You can actually view the database schema with the data type and length from SQL Server diagrams… :

    1. Create new diagrams fr SQL Server Manager
    2. Add the relevant tables
    3. Right click on the table diagram view, under “Table View -> Modify Custom …” menu, just add the custom columns (default value, length, precision, scale etc) you want

  8. Maybe this does not do exactly what you want… but it’s what I’ve used…

    In Enterprise Manager:

    1) Right click on the database you want to work with

    2) Select All Tasks -> Generate SQL Script…

    3) Click on the Show All button

    4) Select the options you want

    5) Click preview and copy to the clipboard or go through the save process

    You have the schema formatted as an SQL script ready to use or print

  10. Just what I was looking for. Simple, readable, free. In response to some of the comments up above, diagrams/ERDs serve a different purpose, and expanding, copying and pasting 30 or more tables becomes tiresome. Thanks for putting this script together and making it available.

  12. I could use something like this. The link to the file, however, is broken. Could this be fixed and the file made available?

  15. I don’t want to see my tables. I made a diagram with the manager of SQL Server 2000. I would like to plot that on a huge paper, because there are about 15 tabels with different relations.

    Willio

  17. I’m just happy that you took the time to write the asp. Served my purposes perfectly. Thank you very much.

  19. This was (is!) a huge time saver for me. Very, very convenient when I’m working remotely and need to refresh my memory of some table’s schema.

    Thanks so much.

  21. I am the owner of the product, SQL2000Print, thats is mentioned in the opening paragraph ( …but they wanted $59). I would like to say that the product is worth $59. It prints almost ALL the objects (Tables, Views, Stored Procedures, Logins … + all their permissions and dependencies etc. ) in the Database

  22. Absolutely simple to implement (even for a novice) and the script returns much better formatting than the “generate script” option in EM. Served my purpose very well. Thanks for your contribution.

  25. I spent the last 1/2 hour trying to find a suitable solution, since I am using a shared hosting account that doesn’t give me access to Database Diagrams. Your solution was exactly what I was looking for and took me only a minute to set up. I’m using SQL 2005. Thanks for taking the time!

  26. The code is almost as useful as the output! Nice to see XMLHttp in action.

    Thanks for sharing, it was exactly what I was looking for.

  32. Fantastic! Nice and simple to use and does exactly what it says on the tin.
    Many thanks.
    To make it work for me I had to comment out line 4:

    // sConn += “Network Library=DBMSSOCN;”;

  41. @I NEED HELP

    This is a web page written in asp/javascript. In order to run this, you need to setup your OS with IIS (to enable your computer to act as a web server.. Typically Windows 2000, Windows XP Pro, Vista Business or Ultimate) or have a windows server running IIS. It needs to be renamed from index.asp.txt to index.asp.

    It takes a few seconds to implement this and if you arent a web person, google setting up a personal web server or ask someone that has access to a Windows web server.

    Jim,tThanks for a great and simple app, does the job perfectly!

  43. hi how am i know about the schema of table or database and could i know the procedure of making DFD through SQL.

Comments are closed.