Roman's Weekly SQL Server Tip - Viewing SQL Server table structure easily
When you code or troubleshoot database applications, it's very likely is that you often need to look at the structure of your SQL Server tables - what columns there are, column names, datatypes, constraints, relationships to other tables, etc. If you do performance tuning, you probably also need to examine indexes and unique constraint. You can get all this stuff from Enterprise Manager or Query Analyzer. If you're more savvy, you can run a few system stored procedures or queries against INFORMATION_SCHEMA objects. And while I consider EM to be a great tool, I've always thought that I had to do too much drilling to get to the parts of the table I wanted to look at, especially if I want to compare tables in two or more databases. And there still doesn't seem to be an easy way to examine table relationships - I don't want to create a dummy diagram just to see the parent and the child tables.
So eventually I wrote a simple, but pretty handy tool called TableInfo. My design goal was to have a utility that would let me get to the most useful table metadata within 5 seconds and provide a comprehensive view of columns, relationships, indexes, constraints and table security. TableInfo prompts you for server name, database name, user name and password, you can also choose Windows authentication. Tables are displayed without the owner prefix, but you can filter by table owner. This way you can still use keyboard shortcuts to scroll down to tables that start with a "P" for example.
I also built in a few command line parameters. You can pass in server, database and login credentials in the following format:
TableInfo ServerName, DatabaseName, UserName, Password
This functionality allows you to register TableInfo as an external tool in both Enterprise Manager and Query Analyzer and configure them to send the server name and the database name as command line arguments. So when you're working with one of these tools and want to use TableInfo, you can launch it and open in the same database you're working in. Query Analyzer can also send the user name, but not the password. It used to until SP3, but no more. Maybe Don Kiely got on their case?
You can download TableInfo from this location. It's a .NET WinForm executable so there is no install, just drop the files to a folder. The zip file also contains a document that shows in more detail how to register TableInfo with EM and QA.
Please let me know about any bugs you find and I would also love to get some feedback or feature suggestions.
