My client wanted a list of all of the tables and their respective columns in a given SQL server database. My first instinct was to get Visio to create an ER diagram by reverse engineering the database. However, with dozens of tables in this database, the diagram that Visio spit out looked like spaghetti and meatballs, and would have printed across 36 8.5″x11″ pages. That said, Visio did a better job laying out the diagram than I would have by hand.
Then I thought, what if I had an automated way to create a document that listed the tables in alphabetical order, showing all of their columns? This list would be something that the client could cross-refrence against the spaghetti monster. Well, thanks to T4, I do have such an automated way. I spent my lunch break hacking away (it’s scary to see how much you rely on Intellisense once you don’t have it) at this simple template that will iterate through all the tables in a database and output all the columns for each as a text file. To get it work,follow these steps:
- Download the template file (all_table_columns.tt).
- Copy it to any Visual Studio 2005/2008 project.
- Open it, replace “[your connection string here]” with a valid SQL Server connection string (ideally to a database with a limited number of tables), and then save the file.
Visual Studio attempts to generate the output whenever you save the template file, so the new text file should already be generated. To view it, go to the Solution Explorer window and expand the node for all_table_columns.tt and you will see a file named all_table_columns.txt. Open that text document and you should have a list of all tables and columns!
For more on code generation via T4, see Scott Hanselman’s link-fest of a post.