Handy Script Helps Analyze Database Structures

To analyze current database structures, you need to identify what columns are in what tables. The script in Listing 1 can help with this tedious task. This script creates a function that identifies all the tables a column comes from. The script also creates a stored procedure that dynamically creates a select list of table names with the results identifying which table contains what column. I'm usually interested in tables that share a common column (e.g., LastName, City, Phone), so the stored procedure is tailored to this requirement.

I run the stored procedure from inside Excel. To do this, choose Data, Get External Data, New Database Query. Go to the Database tab and set up a New Database Source. After entering a name, select SQL Server as the driver and click Connect. In the SQL Server Login dialog box, enter your server’s name and the login information. Click the Options button and select your database. After the source is set up, the Query Wizard tries to walk you through building a query. Because you just want to run a stored procedure, click Cancel. When you’re asked whether you want to continue editing the query, click Yes. Microsoft Query then brings you to another query builder (adding tables), at which point you should click Close. Under the View menu, select SQL, which brings up a window that lets you type in any valid SQL statement. Enter the command to run the stored procedure (e.g., exec ColumnUsageDiscovery 'LastName'), and click OK. When Query warns you that it can’t be represented graphically, click OK. The stored procedure then executes. To get the results into Excel, choose File, Return Data to MS Excel.

After MS Query returns the results from the stored procedure, I place the results in cell A1. I format Row 1 so that the text runs vertical by selecting Cells under the Format menu, clicking the Alignment tab, then changing the orientation to 90 degrees. I then format all the column widths by highlighting the columns, selecting Columns under the Format menu, then selecting the AutoFit Selection option. What I end up with is column names down the left followed by small cells that identify which tables each column belongs to.

— Lawrence Rogers
[email protected]

Editor’s Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.