Printing SQL Server Database Schema
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!
Now if I could only find a way to use a schema.ini file to create tables in SQL Server 2000…
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!
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.
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.
I had always just copied all the rows in Design view and pasted into Excel. Resize a couple of columns and print away.
Thanks for pointing out the markup error! Note to self – always validate before publishing!
As for the printing – I’ll look into it!
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
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
The printing problem has been fixed. Looks like IE and Firefox have problems printing absolutely positioned elements.
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.
Link for source is broken
I could use something like this. The link to the file, however, is broken. Could this be fixed and the file made available?
Link is stil broken! This sounds perfect for what I was looking for. Can someone please email me when fixed?
Link is fixed - enjoy!
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
After reading the comments, I wasn’t sure that your code would be worth my time.
However, I implemented it anyhow, and I must say that I do find it useful.
Thanks!
I’m just happy that you took the time to write the asp. Served my purposes perfectly. Thank you very much.
This was great. Thank you.
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.
perfect site good information, very nice news and etc… tnx
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
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.
Useful script and good example of XMLHttp !!
Excellent piece of code! Thanks.
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!
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.
Please please tell me how I would print a table using sql .. ie the contents of a table. Thanks
All I seem to get is Page cannot be displayed? Any ideas?
Good job!, very helpfull!!.
Cheers
Very Very Nice.. Thanks.
VERY helpful– many thanks!
Now if MS would just build this simple need directly into their apps…
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;”;
Simply perfect!…Got it up and running in seconds. Thank you very much.
Very new to web based applications and SQL 2005 and facing a major deadline. Boy did this help!!! Thanks for posting this.
this is awesome!!! Quick and dirty - love it! Thanks
Thanks so much Jim! Simple, clean, exactly what I needed!
Very nice job. Does exactly what it says and is easy to implement.
Keep up the good work.
Good job for the poor M$SQL.
Other suggestion:
http://www.sqlmag.com/Article/ArticleID/15456/sql_server_15456.html
Thanks for this, I am a sql server newbie and this took seconds to set up.
How do you run this ?
@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!
Thanks… just saved 59$
hi how am i know about the schema of table or database and could i know the procedure of making DFD through SQL.