12 August 2014

Quickly Investigating What’s in the Tables of SQL Server Databases

From SQL Server Management Studio it is difficult to look through the first few rows of a whole lot of tables in a database. This is odd, since it is a great way to get quickly familiar with a database. Phil was persuaded to tidy up a SQL routine he uses to investigate databases quickly in a browser. He explains how to use it, how it works, and how to use it from PowerShell.

It is very handy to get an impression of a database, or a schema within a database, by being able to see what’s in the first few rows of all the tables. I like to use a browser to do this so as I get the search and zoom facilities for free. It also means I can keep a whole lot of databases or schemas loaded up into browser tabs if necessary.

Every SQL developer has a whole lot of routines that they use in order to more quickly make sense of the databases they work on. Almost everyone I know who is working in the same business has their own different collection of code, often stored on a thumbdrive, that evolves over time. What gets into that ‘toolbox’ is very much an idiosyncratic choice. There are gems that everyone seems to have such as Kimberly Tripp’s routine for finding duplicate indexes, and Adam Machanic’s sp_whoisactive: However, most of the cherished routines in the average thumbdrive seem to be unfinished hacks that just come in handy. Because they’re unfinished and a bit untidy you tend not to see them. I have quite a few that I use regularly to investigate a database. One routine I find particularly useful is a simple thing that simply prints out the first three lines of a collection of tables that I specify. This is a curiously useful way of getting a good overview of what a database, or more usually a schema within a database, is really doing and how it is designed. It actually produces an HTML document that produces an index at the top that can be used to navigate to the table you want to examine. You can either email this from the database or export it as a file to view in a browser. In PowerShell, you can execute it as a query, save the HTML as a local file, and invoke the browser to display the results.

Although the routine is pretty simple, there are a few obvious problems. You can’t really display the big values such as BLOBs and long strings. I just display the first 100 characters. NULLs can cause problems until you know the right XML spell, if a cell contains HTML it has to be cleaned up to avoid rendering problems. On a large database, you will only want to display the contents of a single schema, or a range of tables. (a 1000-table database took six minutes to do on test!)

I find it just plain useful although some may rub their eyes at such a humble utility. I offer it in all its unkempt charm in the hope that someone else will find it a good way of getting to know a database.

The Beef

 Here is the old PUBS database. It is just enough to show you what the routine will do. If you have a realistic database, you’ll have a lot of tables so the catalog or contents page in the beginning which you click on to get to the table you wish to view becomes more useful!

au_id au_lname au_fname phone address city state zip contract
172-32-1176 White Johnson 408 496-7223 10932 Bigge Rd. Menlo Park CA 94025 1
213-46-8915 Green Marjorie 415 986-7020 309 63rd St. #411 Oakland CA 94618 1
238-95-7766 Carson Cheryl 415 548-7723 589 Darwin Ln. Berkeley CA 94705 1
discounttype stor_id lowqty highqty discount
Initial Customer NULL NULL NULL 10.50
Volume Discount NULL 100 1000 6.70
Customer Discount 8042 NULL NULL 5.00
emp_id fname minit lname job_id job_lvl pub_id hire_date
PMA42628M Paolo M Accorti 13 35 0877 1992-08-27T00:00:00
PSA89086M Pedro S Afonso 14 89 1389 1990-12-24T00:00:00
VPA30890F Victoria P Ashworth 6 140 0877 1990-09-13T00:00:00
job_id job_desc min_lvl max_lvl
1 New Hire – Job not specified 10 10
2 Chief Executive Officer 200 250
3 Business Operations Manager 175 225
pub_id logo pr_info
0736 R0lGODlh0wAfALMPAAAAAIAAAACAAICAAAAAgIAAg This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is
0877 R0lGODlhiwAvALMPAAAAAIAAAACAAICAAAAAgIAAg This is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley
1389 R0lGODlhwgAdALMPAAAAAIAAAACAAICAAAAAg This is sample text data for Algodata Infosystems, publisher 1389 in the pubs database. Algodata Inf
pub_id pub_name city state country
0736 New Moon Books Boston MA USA
0877 Binnet & Hardley Washington DC USA
1389 Algodata Infosystems Berkeley CA USA
title_id lorange hirange royalty
BU1032 0 5000 10
BU1032 5001 50000 12
PC1035 0 2000 10
stor_id ord_num ord_date qty payterms title_id
6380 6871 1994-09-14T00:00:00 5 Net 60 BU1032
6380 722a 1994-09-13T00:00:00 3 Net 60 PS2091
7066 A2976 1993-05-24T00:00:00 50 Net 30 PC8888
stor_id stor_name stor_address city state zip
6380 Eric the Read Books 788 Catamaugus Ave. Seattle WA 98056
7066 Barnum’s 567 Pasadena Ave. Tustin CA 92789
7067 News & Brews 577 First St. Los Gatos CA 96745
au_id title_id au_ord royaltyper
172-32-1176 PS3333 1 100
213-46-8915 BU1032 2 40
213-46-8915 BU2075 1 100
title_id title type pub_id price advance royalty ytd_sales notes pubdate
BU1032 The Busy Executive’s Database Guide business 1389 19.9900 5000.0000 10 4095 An overview of available database systems with emphasis on common business applications. Illustrated 1991-06-12T00:00:00
BU1111 Cooking with Computers: Surreptitious Balance Sheets business 1389 11.9500 5000.0000 10 3876 Helpful hints on how to use your electronic resources to the best advantage. 1991-06-09T00:00:00
BU2075 You Can Combat Computer Stress! business 0736 2.9900 10125.0000 24 18722 The latest medical and psychological techniques for living with the electronic office. Easy-to-under 1991-06-30T00:00:00

Here, in a separate HTML page is AdventureWorks2012, done the same way.

The start of the page looks a bit like this (seen from a distance)


The tables from adventureWorks, seen from a browser (thumbnail)

When I’m using this, I use a SQL Script executed in PowerShell for a development group of servers in my registered server collection in SSMS. I set a task going on the scheduler to create HTML files on my local workstation, one for each database or schema that I can then browse when I need to. However, I’ll give enough information so you can use it in a number of different ways that suit you.

Selecting tables

There is a task here to select a list of tables. I like to specify tables using wildcards so that ‘p%.p%’ would mean all tables whose names beginning with the letter ‘P’, from a schema beginning with the letter ‘P’ (seventeen in AdventureWorks for 2008), or ‘%.%’ for all tables from all schemas. You could do things like ‘%.%log’ for all log tables, but beware of using square brackets in LIKE expressions such as %[^A-Z0-9]%. These get trashed by the PARSENAME function which tends to return a NULL!

Once you have your list of tables, then it is pretty-well plain sailing. I tend to use something like this for any sort of exploration of tables, such as for producing pretty CREATE statements for tables.

Displaying the list of tables.

In HTML, I like to have multi-column lists of tables just to make it a bit easier to scan through. For these, the HTML is just a straightforward list.  The formatting is all in the CSS! This is the easiest way of doing it, using the table variable we already have; though I tend to do the contents list in step with each table.

Displaying the contents of the first three rows

You just need to execute  SQL Queries to get the first three rows of each table to return as XML, and then use this XML document to produce an HTML table. There are a couple of problems to using SELECT * from <tablename>  FOR XML PATH

Illegal column names

If it comes across a column name that is illegal in XML terms, it throws its hands up and errors out.

Try, for example…

And you get…

You actually have to specify the columns, with XML-legal aliases if necessary, which takes some of the advantages away from using the XML trick. E.g.

However, you still get automatic truncation and conversion of the values, which is handy.

So doing this …

… will give you this:











… and so it is just a matter of getting the next two row values and formatting it all up in an HTML table.

CLR Datatypes

One other problem you’ll hit is that The FOR XML phrase will cause SELECT * FROM to crash if there is a CLR data type in the table. Try, for example…

and you get ….

so instead, you need to  do …

..and by doing an explicit conversion, everything works happily.


You’ll notice that we’ve specified ELEMENTS XSINIL, which gives us NULL values. Unless you do this, XML assumes that the column doesn’t exist for that row, which is pretty reasonable  for a data document. However, it isn’t correct for a SQL table.

Putting it all together

The devil is in the detail, as any SQL Developer will tell you.

Here is the batch. This would normally be made into a stored procedure, but I tend to use such things from SSMS query window or from PowerShell, and for the latter I don’t like to rely on anything in particular being installed. I’m saving the HTML in a file that the browser can display. I like to have a separate file for each database, so I just specify the path to the directory where the file should be and leave it to the routine to name the file, using the database and server name automatically. In this version of the batch, I use an old routine of mine, published elsewhere, called spSaveTextToFile to save the text file to disk, but you can execute the batch from PowerShell to save the file locally instead, and avoid the need to have xp_cmdshell enabled on the server.

I would feel flattered if you had a PhilFactor database on your server as I have, as you’ll see in the reference philfactor.dbo.spSaveTextToFile, but you’ll probably have to install it in your utility directory and reference it from there. Also, I’ve set the file path in the variable @directory to 'd:\files\' (note the trailing backslash. whereas you will probably need to set it to a suitable directory on the server. As mentioned before, you may wish just to select certain tables in certain schemas so you’ll need to set the value of @WildCardName to something suitable!

The CSS is written using the principles and some of the CSS, from this article Making HTML tables easier on the eye- CSS Structural Pseudo-classes.

Putting It In PowerShell

I us a PowerShell routine that uses the function 'Foreach-DatabaseInServers‘ that I published a couple of years ago here ‘PowerShell SMO: Just Writing Things Once’. This routine is very close to what I’ve done in the previous section except for the method of collecting the HTML file and the name of the file to save it in.

This routine allows me to specify what databases I’d like processed on these servers and which of the development servers I want to access.

To run this, the function must be on a file in the same directory as the PowerShell file and if you are running it in the IDE, then the IDE must be on the same directory as well.

There isn’t much PowerShell in this routine . It is mostly the same SQL you’ve just been staring at in admiration (gulp) in the beginning of the article. I’ve done this to show how easy it is to execute SQL on a number of databases in a number of servers, and get the results back.

Getting it running

Now, before you run this, makes sure that,’ unless you happen to have servers called ‘Shem’, ‘Ham’ and ‘Japeth’, you will need to change the line

to something else.

I always use my Central Management Server groups, rather than do lots of handmade lists of my servers (fortunately they are all within the domain so I can use Windows Security)

You will also need to change the …

… to point to a valid directory. You might  also want to change or remove the database filter …

… If  you only want to list out Adventureworks databases it would be ‘ADV*’. ( you can use this to exclude databases)


This is a demonstration of the type of routine that some DBAs or Database Developers will find very handy, though it will probably not be universal enough to embed in a commercial tool. It is the sort of script-based tool that most of us working on SQL Server carry around on thumbdrives and which generally exist in slightly tatty states. PowerShell has made it so much easier to use custom scripts like this, and has freed us from much of the tedium of having to use .NET languages like C#, VB or Python to do the automation side.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


Tags: , , , ,


  • Rate
    [Total: 28    Average: 4.7/5]

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Follow on

View all articles by Phil Factor

  • DeafProgrammer

    Excellent Article…
    I love reading his articles. Keep it going mate… Thanks !!

  • Anonymous

    FOR XML and CLR types

    This seems a very helpful utility, but I am facing a problem when running it. I am a SQL beginner, so not sure how to fix it.

    I am running it on AdventureWorks2008 from with in SSMS > Query editor. I get this error message

    Msg 6865, Level 16, State 1, Line 1
    FOR XML does not support CLR types – cast CLR types explicitly into one of the supported types in FOR XML queries.

    I guess it is coming from
    SELECT ‘,[‘+name+’] AS [‘+RedactedName+’] ‘
    FROM @columnsToDo
    ORDER BY column_ID
    because when I comment out this line, the error goes away (even though the logic then would not make much sense)

    Can you please help?

  • Anonymous

    FOR XML and CLR types
    More details on the above problems – Even though AdventureWorks2008, my SQL server is SQL Express 2012.

  • Phil Factor

    Re: FOR XML and CLR types
    No, it is my fault. Geography types and other CLR types have to have an explicit conversion before they can be used with FOR XML. I’ve done a fix and the article has been slightly updated! (I originally tested with the wrong version of Adventureworks!) It should work with all CLR types now but let me know if it fails on any!

  • Anonymous

    working example
    I really like the idea and tutorial. I did get stuck at this part as I kept getting the following error.
    "Must declare the table variable "@tablesToDo"."
    Declare @Contents Varchar(MAX)

    Select @Contents=coalesce(@Contents,”)+'<li><a href="#table’+convert(Varchar(5),TheOrder)+’">’+TheTable+'</a></li>

    ‘ FROM @tablesToDo

    Select @contents='<ol>’+@contents+'</ol>’

  • Phil Factor

    Re working example
    Sorry, but that code snippet in the section ‘displaying the list of tables’ was there just to illustrate how it was done, and isn’t executable. it would probably be best to use the entire batch in the paragraph ‘putting it all together’, up to the point where there is a comment ‘and we loop through each table’ , adding the code from the illustration.

  • Anonymous

    Not nearly as fancy…..
    My preferred method is brutally simple:

    use Database

    execute sp_msforeachtable ‘select top 10 ”?” as Tablename, * from ?’

    Where Database is the name of the DB I want to look at. Like I mentioned in the subject – not fancy but gets the job done for me.

  • Anonymous

    Great stuff
    Love your server names…
    Great tool in the belt.

  • Phil Factor

    Re: not nearly as fancy
    gosh, coincidence. That’s the way I used to have to do it!

  • Phil Factor

    Re: great stuff.
    It is possible to alias your registered servers when they have long complicated names that you don’t want anyone on GitHub or the interwebs to see.

  • cmkony04

    Hi Phil
    Great Stuff
    Could you please help:
    1. I copy the sql code
    2. run the store procedure to call at the end.
    3. Rename the database name with mine,

    But I am receiving the error message:
    Msg 9420, Level 16, State 1, Line 1
    XML parsing: line 1, character 4548, illegal xml character


  • Phil Factor

    Re: Wow.
    Yes, I had this error during development, but fixed the problem on all my test databases. It happened on databases that had ‘illegal’ characters in column names, though I thought I’d caught and removed all the possible bad characters, so I guess that this is something else. Anyway, it is easy to find out.
    The error is happening at the line

    EXECUTE sp_ExecuteSQL @statement = @SQL,
    @params = N’@TheXML XML OUTPUT’,
    @TheXML = @x output

    You will need to capture the contents of the @SQL variable and execute it against the database in a query window. This is likely to tell you what is going wrong at character 4548!

  • cmkony04

    Thank you
    Thank you for your assistance.

  • ogrish

    got error
    Hi Phil,

    I found when you have a column name that starts with a number (I know this is bad but I have some this kind of columns for historical reasons) your script will generate an error something similar to:

    Exception calling "ExecuteWithResults" with "1" argument(s): "Execute with results failed for Database ‘test’. "
    At C:temptestWhatsInThoseTables.ps1:185 char:8
    + $result=$database.ExecuteWithResults($SQL) #execute the SQL
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

    You can easily reproduce this issue with this simple table:

    CREATE TABLE badtable(id INT, [3_haha] int)

    The error comes from this generated statement (just an example):

    SELECT TOP (3)
    [id] AS [id],
    [3_haha] AS [3_haha]
    FROM [dbo].[badtable]

    I tried but still can’t figure out how to fix it. Hope you can take a look. This script is really useful. Thank you!