Click here to monitor SSC
Phil Factor

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

12 August 2014

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!

[dbo].[authors]
au_idau_lnameau_fnamephoneaddresscitystatezipcontract
172-32-1176WhiteJohnson408 496-722310932 Bigge Rd.Menlo ParkCA940251
213-46-8915GreenMarjorie415 986-7020309 63rd St. #411OaklandCA946181
238-95-7766CarsonCheryl415 548-7723589 Darwin Ln.BerkeleyCA947051
[dbo].[discounts]
discounttypestor_idlowqtyhighqtydiscount
Initial CustomerNULLNULLNULL10.50
Volume DiscountNULL10010006.70
Customer Discount8042NULLNULL5.00
[dbo].[employee]
emp_idfnameminitlnamejob_idjob_lvlpub_idhire_date
PMA42628MPaoloMAccorti133508771992-08-27T00:00:00
PSA89086MPedroSAfonso148913891990-12-24T00:00:00
VPA30890FVictoriaPAshworth614008771990-09-13T00:00:00
[dbo].[jobs]
job_idjob_descmin_lvlmax_lvl
1New Hire - Job not specified1010
2Chief Executive Officer200250
3Business Operations Manager175225
[dbo].[pub_info]
pub_idlogopr_info
0736R0lGODlh0wAfALMPAAAAAIAAAACAAICAAAAAgIAAgThis is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is
0877R0lGODlhiwAvALMPAAAAAIAAAACAAICAAAAAgIAAgThis is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley
1389R0lGODlhwgAdALMPAAAAAIAAAACAAICAAAAAgThis is sample text data for Algodata Infosystems, publisher 1389 in the pubs database. Algodata Inf
[dbo].[publishers]
pub_idpub_namecitystatecountry
0736New Moon BooksBostonMAUSA
0877Binnet & HardleyWashingtonDCUSA
1389Algodata InfosystemsBerkeleyCAUSA
[dbo].[roysched]
title_idlorangehirangeroyalty
BU10320500010
BU103250015000012
PC10350200010
[dbo].[sales]
stor_idord_numord_dateqtypaytermstitle_id
638068711994-09-14T00:00:005Net 60BU1032
6380722a1994-09-13T00:00:003Net 60PS2091
7066A29761993-05-24T00:00:0050Net 30PC8888
[dbo].[stores]
stor_idstor_namestor_addresscitystatezip
6380Eric the Read Books788 Catamaugus Ave.SeattleWA98056
7066Barnum's567 Pasadena Ave.TustinCA92789
7067News & Brews577 First St.Los GatosCA96745
[dbo].[titleauthor]
au_idtitle_idau_ordroyaltyper
172-32-1176PS33331100
213-46-8915BU1032240
213-46-8915BU20751100
[dbo].[titles]
title_idtitletypepub_idpriceadvanceroyaltyytd_salesnotespubdate
BU1032The Busy Executive's Database Guidebusiness 138919.99005000.0000104095An overview of available database systems with emphasis on common business applications. Illustrated1991-06-12T00:00:00
BU1111Cooking with Computers: Surreptitious Balance Sheetsbusiness 138911.95005000.0000103876Helpful hints on how to use your electronic resources to the best advantage.1991-06-09T00:00:00
BU2075You Can Combat Computer Stress!business 07362.990010125.00002418722The latest medical and psychological techniques for living with the electronic office. Easy-to-under1991-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!

DECLARE @WildCardName VARCHAR(150) --the wildcard to represent the tables you want

DECLARE @tablesToDo TABLE(TheOrder INT IDENTITY,TheTable VARCHAR(2000))

    --this is the table variable containing our list of tables

 

SELECT @WildCardName ='%.%';

 

    --if no Schema was specified, we'll assume all schemas are intended

IF PARSENAME(@WildCardName,2) IS NULL

    SELECT @WildCardName='%.'+@WildCardName;

    --now we fetch all the table names into the table

INSERT INTO @tablesToDo (TheTable) --insert the names in order into the table

    SELECT QUOTENAME(schema_name([schema_ID]))+'.'+QUOTENAME(name)

      FROM sys.tables

      WHERE name LIKE PARSENAME(@WildCardName,1)--the table name

        AND schema_name([schema_ID]) LIKE PARSENAME(@WildCardName,2)--the schema

      ORDER BY schema_name([schema_ID]),name; --order by schema, followed by name

    --nothing found? We warn the user and abort.

IF @@rowcount=0 --if we found nothing

    BEGIN

    RAISERROR  ('<p>No such table like ''%s'' in this database</p>',16,1,@WildCardName);

        --return 1

    END

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.

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>'

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…

Select * from Adventureworks.dbo.AWBuildVersion for xml path

And you get…

Msg 6850, Level 16, State 1, Line 1

Column name 'Database Version' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault.

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.

Select SystemInformationID, [Database Version] as DatabaseVersion, VersionDate, ModifiedDate

from Adventureworks.dbo.AWBuildVersion for xml path

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

So doing this ...

DECLARE @XML XML;

Select @XML=(Select SystemInformationID, [Database Version] as DatabaseVersion, VersionDate, ModifiedDate

from Adventureworks.dbo.AWBuildVersion for xml path, ELEMENTS XSINIL, root);

SELECT  [x].value('local-name(.)', 'varchar(100)'),

                     [x].value('text()[1]','varchar(100)')

         FROM @XML.nodes('root/row[1]/*')  as a(x);

... will give you this:

Column_Name

The_Value

SystemInformationID

1

DatabaseVersion

10.00.80404.00

VersionDate

2008-04-04T00:00:00

ModifiedDate

2008-04-04T00:00:00

... 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...

Select * from person.address for XML path

and you get ....

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

so instead, you need to  do ...

Select AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode, convert(varchar(100),SpatialLocation), rowguid, ModifiedDate from person.address for XML path

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

NULLs

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.

Set nocount on

SET ARITHABORT ON      

DECLARE @WildCardName VARCHAR(150) --the wildcard to represent the tables you want

DECLARE @Directory VARCHAR(255)--the full path of the directory where you want to store the file

    --this is the table variable containing our list of tables

SELECT @WildCardName ='%.%', @Directory='d:\files\'

DECLARE @FileNameAndPath VARCHAR(255) --the path and the file

DECLARE @ColumnList NVARCHAR(MAX) --comma delimited list of columns

DECLARE @x XML, @HTML VARCHAR(MAX), @Row VARCHAR(MAX), @CrLf CHAR(2)

DECLARE @Contents NVARCHAR(MAX)

DECLARE @Errors NVARCHAR(MAX)

DECLARE @SQL NVARCHAR(MAX) --the dynamic SQL that we create

DECLARE @ii INT ,@iiMax INT --the counters for our loop

DECLARE @TheTable VARCHAR(2000) --the name of the table being documented

DECLARE @tablesToDo TABLE(TheOrder INT IDENTITY,TheTable VARCHAR(2000))

    --if no Schema was specified, we'll assume all schemas are intended

DECLARE @columnsToDo TABLE (FirstBadCharacter int ,name varchar(255), RedactedName varchar(255), column_ID int, Is_Assembly_Type bit )

 

IF PARSENAME(@WildCardName,2) IS NULL

    SELECT @WildCardName='%.'+@WildCardName;

    --now we fetch all the table names into the table

INSERT INTO @tablesToDo (TheTable) --insert the names in order into the table

    SELECT QUOTENAME(schema_name([schema_ID]))+'.'+QUOTENAME(name)

    FROM sys.tables

    WHERE name LIKE PARSENAME(@WildCardName,1)--the table name

        AND schema_name([schema_ID]) LIKE PARSENAME(@WildCardName,2)--the schema

    ORDER BY schema_name([schema_ID]),name; --order by schema, followed by name  

    --nothing found? We warn the user and abort.

IF @@rowcount=0 --if we found nothing

    BEGIN

    Select @errors='<H4>No such table like '''+@WildCardName+''' in this database</H4>';

        --return 1

    END

    --and we loop through each table, creating the HTML table that shows the first three rows.

SELECT  @CrLf=CHAR(13)+CHAR(10), @Contents='', @HTML='', @ii=1,@iiMax=MAX(TheOrder)

    FROM @tablesToDo;

WHILE @ii<=@iiMax

    BEGIN

        --get the name and schema of the next table to do

    SELECT @TheTable=TheTable,@ii=@ii+1

        FROM @tablesToDo

        WHERE TheOrder=@ii;

    SELECT @Contents=@Contents+'<li><a href="#table'+CONVERT(VARCHAR(5),@ii)+'">'+@TheTable+'</a></li>'+@CrLf;

    /* get the name of the column and take out any problem characters for XML and HTML */

    Delete from @columnsToDo 

    insert into @columnsToDo (FirstBadCharacter,name,RedactedName,Column_ID,is_assembly_type)

       sELECT Patindex('%[^a-zA-Z_0-9]%',sys.columns.NAME COLLATE Latin1_General_CI_AI),sys.columns.Name,

                    sys.columns.Name,Column_ID,is_assembly_type            

         FROM sys.columns

              inner join sys.types

              on sys.columns.user_type_id =sys.types.user_type_id

        WHERE OBJECT_NAME([object_id]) LIKE PARSENAME(@TheTable,1)

        AND object_schema_name([object_ID]) LIKE PARSENAME(@TheTable,2)

    while exists (Select * from @columnsToDo where FirstBadCharacter>0)

              Begin

              update @columnsToDo

                     Set RedactedName= stuff(RedactedName,FirstBadCharacter,1,'_') where FirstBadCharacter>0

              update @columnsToDo

                     Set FirstBadCharacter=Patindex('%[^a-zA-Z_0-9]%',RedactedName COLLATE Latin1_General_CI_AI)

                       where FirstBadCharacter>0

              end

    SELECT @ColumnList=STUFF((

           SELECT ','+case when is_assembly_type=1 then 'CONVERT(VARCHAR(2000),['+name+']) AS ['+RedactedName+'] '

            else '['+name+'] AS ['+RedactedName+'] ' end

                FROM @columnsToDo

                ORDER BY column_ID

                        FOR XML PATH (''), TYPE).value('.', 'varchar(max)') ,1,1,'');

        --get the top three rows (meaningless as we haven't specified the order) as XML

    SELECT @SQL=N'Select @TheXML=((Select top 3 '+@columnList+' from '+@TheTable

                +' for XML path, ELEMENTS XSINIL, root))'

    EXECUTE sp_ExecuteSQL  @statement =  @SQL,

        @params = N'@TheXML XML  OUTPUT',

        @TheXML = @x output

        --now we do the TABLE tag in HTML with the name of the table and the caption

    SELECT @HTML=@HTML+'<table id="table'+CONVERT(VARCHAR(5),@ii)+'" class="tablecontents" border="1"

summary="first three rows in table'''+@TheTable+'''">

<caption>'+@TheTable+'</caption>

<thead>

<tr>

',@Row=''

        --if no data there then we just give the column names taken from the system tables.

    IF @X IS NULL --no XML output from the executed batch

        BEGIN --just adding in all the column names in the header

        SELECT @HTML=@html+'<th>'+name+'</th>'+@CrLf

            FROM @columnsToDo

            ORDER BY column_ID

        SELECT @HTML=@HTML+'</tr></thead>'

        END

    ELSE--it was valid XML result so there was data.

        BEGIN

            --get the heading line for the column names 

        SELECT  @HTML=@HTML+'<th>'+[x].value('local-name(.)', 'varchar(100)')+'</th>',--+@CrLf,

            @Row=@row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'</td>'--+@CrLf

            FROM @x.nodes('root/row[1]/*')  AS a(x)

            --and add it to the table

        SELECT @html=@HTML+'</tr></thead>'+@CrLf+'<tbody><tr>'+@Row+'</tr>'+@CrLf, @row=''

            --now we collect the data from  any second row

        SELECT @Row=@Row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'</td>'--+@CrLf

            FROM @x.nodes('root/row[2]/*')  AS a(x)

            --if there was a second row we add it.

        IF @@Rowcount>0

            SELECT @html=@HTML+'<tr>'+@Row+'</tr>'+@CrLf, @row=''

            --now we get the third row if there is one

        SELECT @Row=@Row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'</td>'-- +@CrLf

            FROM @x.nodes('root/row[3]/*')  AS a(x)

        IF @@Rowcount>0

            SELECT @html=@HTML+@CrLf+'<tr>'+@Row+'</tr>',@row=''

        END

    SELECT @html=@HTML+'</tbody></table>'

    END

/* now we have all the data we need, we turn it into an HTML page merely by adding the CSS and the basic page elements */   

SELECT @HTML='<!DOCTYPE html>

<html>

<head>

<title>'+@@Servername+'-' +DB_NAME()+'</title>

</head>

<style>

<!--

 

.columnar { columns: 4; -moz-column-width: 15em; -webkit-column-width:15em; column-width: 15em; }

 

.thetables { }

 

/* do the basic style for the entire table */

.thetables table {

   border-collapse: collapse;

   border: none ;

   font: 11px Verdana, Geneva, Arial, Helvetica, sans-serif;

   color: black;

   margin-left:20px;

   margin-top: 20px;

 

    }

/*attach the styles to the caption of the table */

.thetables table caption {

  font-weight: bold;

  text-align:left;

  padding-left:5px;

  background-color: #f3f3f3;

}

  

/*give every cell the same style of border */

.thetables table td, .thetables table th, .thetables table caption { border: 1px solid #bbbde1  ;  vertical-align: top;  }

/* apply styles to the odd headers */

.thetables table th:nth-child(odd) { background-color: #cedfe2; }

/* apply styles to the even headers */

.thetables table tr th:nth-child(even) { background-color: #dfebee; }

 /* apply styles to the even rows */

.thetables table td {background-color: #f0f7f9;}

 

.thetables table tr:nth-child(even) td:nth-child(odd){background-color: #f7fafb;  }

 /* apply styles to the even colums of odd rows */

.thetables table tr:nth-child(odd) td:nth-child(even){ background-color: #f7fafb; }

 

h1, ol { color: #000000; text-align: left; font: normal 11px Verdana, Geneva, Arial, Helvetica, sans-serif; }

 

h1 { font-size: 16px; font-weight: bold; color: #000000; text-align: left; }

-->

</style>

 

<body>

<h1>Sample of contents of tables in '+@@Servername+'-'+DB_NAME()+'</h1>

<div class="columnar"><ol>'+@Contents+'</ol></div>

<div class="thetables">   

'    +Coalesce(@HTML,'')+coalesce(@errors,'')+'

</div>

</body>

</html>'

/* and now we write out the result */

SELECT @FileNameAndPath=@Directory+REPLACE(REPLACE(REPLACE(@@Servername+'-'+DB_NAME(),'/',''),'\',''),':','')+'.html'

EXECUTE philfactor.dbo.spSaveTextToFile @html,@FileNameAndPath 

 

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

$servers=@('Shem’,’Ham’,’Japeth’) #Specify the list of servers you want to use

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)

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

#get a list of the servers we want to scan

$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

You will also need to change the ...

$LocalDirectory='E:\MyDatabaseTables\'#the path to the local directory where you want to store it

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

-TheDatabaseFilter  { param($x); if ($x.name -like '*'){$x}}

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

 

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

$servers=@('Shem’,’Ham’,’Japeth’) #Specify the list of servers you want to use

$TableWildcard='%.%'#Which schema and tables do you want to do (%.% means all tables from all schema

$LocalDirectory='E:\Tables\'#the path to the local directory where you want to store it

$SQL=@"

Set nocount on

SET ARITHABORT ON        

DECLARE @WildCardName VARCHAR(150) --the wildcard to represent the tables you want

DECLARE @Directory VARCHAR(255)--the full path of the directory where you want to store the file

    --this is the table variable containing our list of tables

SELECT @WildCardName ='$TableWildcard', @Directory='$LocalDirectory'

DECLARE @FileNameAndPath VARCHAR(255) --the path and the file

DECLARE @ColumnList NVARCHAR(MAX) --comma delimited list of columns

DECLARE @x XML, @HTML VARCHAR(MAX), @Row VARCHAR(MAX), @CrLf CHAR(2)

DECLARE @Contents NVARCHAR(MAX)

DECLARE @Errors NVARCHAR(MAX)

DECLARE @SQL NVARCHAR(MAX) --the dynamic SQL that we create

DECLARE @ii INT ,@iiMax INT --the counters for our loop

DECLARE @TheTable VARCHAR(2000) --the name of the table being documented

DECLARE @tablesToDo TABLE(TheOrder INT IDENTITY,TheTable VARCHAR(2000))

    --if no Schema was specified, we'll assume all schemas are intended

DECLARE @columnsToDo TABLE (FirstBadCharacter int ,name varchar(255), RedactedName varchar(255), column_ID int, Is_Assembly_Type bit )

 

IF PARSENAME(@WildCardName,2) IS NULL

    SELECT @WildCardName='%.'+@WildCardName;

    --now we fetch all the table names into the table

INSERT INTO @tablesToDo (TheTable) --insert the names in order into the table

    SELECT QUOTENAME(schema_name([schema_ID]))+'.'+QUOTENAME(name)

    FROM sys.tables

    WHERE name LIKE PARSENAME(@WildCardName,1)--the table name

        AND schema_name([schema_ID]) LIKE PARSENAME(@WildCardName,2)--the schema

    ORDER BY schema_name([schema_ID]),name; --order by schema, followed by name  

    --nothing found? We warn the user and abort.

IF @@rowcount=0 --if we found nothing

    BEGIN

    Select @errors='<H4>No such table like '''+@WildCardName+''' in this database</H4>';

        --return 1

    END

    --and we loop through each table, creating the HTML table that shows the first three rows.

SELECT  @CrLf=CHAR(13)+CHAR(10), @Contents='', @HTML='', @ii=1,@iiMax=MAX(TheOrder)

    FROM @tablesToDo;

WHILE @ii<=@iiMax

    BEGIN

        --get the name and schema of the next table to do

    SELECT @TheTable=TheTable,@ii=@ii+1

        FROM @tablesToDo

        WHERE TheOrder=@ii;

    SELECT @Contents=@Contents+'<li><a href="#table'+CONVERT(VARCHAR(5),@ii)+'">'+@TheTable+'</a></li>'+@CrLf;

    /* get the name of the column and take out any problem characters for XML and HTML */

    Delete from @columnsToDo 

    insert into @columnsToDo (FirstBadCharacter,name,RedactedName,Column_ID,is_assembly_type)

       sELECT Patindex('%[^a-zA-Z_0-9]%',sys.columns.NAME COLLATE Latin1_General_CI_AI),sys.columns.Name,

                    sys.columns.Name,Column_ID,is_assembly_type            

         FROM sys.columns

              inner join sys.types

              on sys.columns.user_type_id =sys.types.user_type_id

        WHERE OBJECT_NAME([object_id]) LIKE PARSENAME(@TheTable,1)

        AND object_schema_name([object_ID]) LIKE PARSENAME(@TheTable,2)

    while exists (Select * from @columnsToDo where FirstBadCharacter>0)

              Begin

              update @columnsToDo

                     Set RedactedName= stuff(RedactedName,FirstBadCharacter,1,'_') where FirstBadCharacter>0

              update @columnsToDo

                     Set FirstBadCharacter=Patindex('%[^a-zA-Z_0-9]%',RedactedName COLLATE Latin1_General_CI_AI)

                       where FirstBadCharacter>0

              end

    SELECT @ColumnList=STUFF((

           SELECT ','+case when is_assembly_type=1 then 'CONVERT(VARCHAR(2000),['+name+']) AS ['+RedactedName+'] '

            else '['+name+'] AS ['+RedactedName+'] ' end

                FROM @columnsToDo

                ORDER BY column_ID

                        FOR XML PATH (''), TYPE).value('.', 'varchar(max)') ,1,1,'');

        --get the top three rows (meaningless as we haven't specified the order) as XML

    SELECT @SQL=N'Select @TheXML=((Select top 3 '+@columnList+' from '+@TheTable

                +' for XML path, ELEMENTS XSINIL, root))'

    EXECUTE sp_ExecuteSQL  @statement =  @SQL,

        @params = N'@TheXML XML  OUTPUT',

        @TheXML = @x output

        --now we do the TABLE tag in HTML with the name of the table and the caption

    SELECT @HTML=@HTML+'<table id="table'+CONVERT(VARCHAR(5),@ii)+'" class="tablecontents" border="1"

summary="first three rows in table'''+@TheTable+'''">

<caption>'+@TheTable+'</caption>

<thead>

<tr>

',@Row=''

        --if no data there then we just give the column names taken from the system tables.

    IF @X IS NULL --no XML output from the executed batch

        BEGIN --just adding in all the column names in the header

        SELECT @HTML=@html+'<th>'+name+'</th>'+@CrLf

            FROM @columnsToDo

            ORDER BY column_ID

        SELECT @HTML=@HTML+'</tr></thead>'

        END

    ELSE--it was valid XML result so there was data.

        BEGIN

            --get the heading line for the column names 

        SELECT  @HTML=@HTML+'<th>'+[x].value('local-name(.)', 'varchar(100)')+'</th>',--+@CrLf,

            @Row=@row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'</td>'--+@CrLf

            FROM @x.nodes('root/row[1]/*')  AS a(x)

            --and add it to the table

        SELECT @html=@HTML+'</tr></thead>'+@CrLf+'<tbody><tr>'+@Row+'</tr>'+@CrLf, @row=''

            --now we collect the data from  any second row

        SELECT @Row=@Row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'</td>'--+@CrLf

            FROM @x.nodes('root/row[2]/*')  AS a(x)

            --if there was a second row we add it.

        IF @@Rowcount>0

            SELECT @html=@HTML+'<tr>'+@Row+'</tr>'+@CrLf, @row=''

            --now we get the third row if there is one

        SELECT @Row=@Row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'</td>'-- +@CrLf

            FROM @x.nodes('root/row[3]/*')  AS a(x)

        IF @@Rowcount>0

            SELECT @html=@HTML+@CrLf+'<tr>'+@Row+'</tr>',@row=''

        END

    SELECT @html=@HTML+'</tbody></table>'

    END

/* now we have all the data we need, we turn it into an HTML page merely by adding the CSS and the basic page elements */   

SELECT @HTML='<!DOCTYPE html>

<html>

<head>

<title>'+@@Servername+'-' +DB_NAME()+'</title>

</head>

<style>

<!--

 

.columnar { columns: 4; -moz-column-width: 15em; -webkit-column-width:15em; column-width: 15em; }

 

.thetables { }

 

/* do the basic style for the entire table */

.thetables table {

   border-collapse: collapse;

   border: none ;

   font: 11px Verdana, Geneva, Arial, Helvetica, sans-serif;

   color: black;

   margin-left:20px;

   margin-top: 20px;

 

    }

/*attach the styles to the caption of the table */

.thetables table caption {

  font-weight: bold;

  text-align:left;

  padding-left:5px;

  background-color: #f3f3f3;

}

  

/*give every cell the same style of border */

.thetables table td, .thetables table th, .thetables table caption { border: 1px solid #bbbde1  ;  vertical-align: top;  }

/* apply styles to the odd headers */

.thetables table th:nth-child(odd) { background-color: #cedfe2; }

/* apply styles to the even headers */

.thetables table tr th:nth-child(even) { background-color: #dfebee; }

 /* apply styles to the even rows */

.thetables table td {background-color: #f0f7f9;}

 

.thetables table tr:nth-child(even) td:nth-child(odd){background-color: #f7fafb;  }

 /* apply styles to the even colums of odd rows */

.thetables table tr:nth-child(odd) td:nth-child(even){ background-color: #f7fafb; }

 

h1, ol { color: #000000; text-align: left; font: normal 11px Verdana, Geneva, Arial, Helvetica, sans-serif; }

 

h1 { font-size: 16px; font-weight: bold; color: #000000; text-align: left; }

-->

</style>

 

<body>

<h1>Sample of contents of tables in '+@@Servername+'-'+DB_NAME()+'</h1>

<div class="columnar"><ol>'+@Contents+'</ol></div>

<div class="thetables">   

'    +Coalesce(@HTML,'')+coalesce(@errors,'')+'

</div>

</body>

</html>'

/* and now we write out the result */

 

--SELECT @FileNameAndPath=@Directory+REPLACE(REPLACE(REPLACE(@@Servername+'-'+DB_NAME(),'/',''),'\',''),':','')+'.html'

--EXECUTE philfactor.dbo.spSaveTextToFile @html,@FileNameAndPath 

Select @html as TheHTML, @Directory+REPLACE(REPLACE(REPLACE(@@Servername+'-'+DB_NAME(),'/',''),'\',''),':','')+'.html' as filename

 

"@

 

<#So, we can make it call some sql and get back a result. In this case we are only looking at the various AdventureWorks databases in all the servers, just to illustrate the different filters you can specify.#>

Foreach-DatabaseInServers $servers -verbose -TheDatabaseFilter  { param($x); if ($x.name -like '*'){$x}} -jobToDo {

   param($database)

       $result=$database.ExecuteWithResults($SQL) #execute the SQL

       $result.Tables[0]

       } |  select-object ('TheHTML','filename') | & {PROCESS{ $_.TheHTML  >$_.filename }} 

Conclusions

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.

Phil Factor

Author profile:

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 :

Google + To translate this article...

Search for other articles by Phil Factor

Rate this article:   Avg rating: from a total of 28 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Excellent Article...
Posted by: DeafProgrammer (view profile)
Posted on: Tuesday, August 12, 2014 at 10:38 PM
Message: I love reading his articles. Keep it going mate... Thanks !!

Subject: FOR XML and CLR types
Posted by: Anonymous (not signed in)
Posted on: Monday, August 18, 2014 at 1:22 PM
Message: Hi,

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
FOR XML PATH (''), TYPE
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?



Subject: FOR XML and CLR types
Posted by: Anonymous (not signed in)
Posted on: Monday, August 18, 2014 at 1:24 PM
Message: More details on the above problems - Even though AdventureWorks2008, my SQL server is SQL Express 2012.

Subject: Re: FOR XML and CLR types
Posted by: Phil Factor (view profile)
Posted on: Monday, August 18, 2014 at 3:47 PM
Message: 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!

Subject: working example
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 20, 2014 at 11:17 AM
Message: 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>'

Subject: Re working example
Posted by: Phil Factor (view profile)
Posted on: Wednesday, August 20, 2014 at 12:31 PM
Message: 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.

Subject: Not nearly as fancy.....
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 20, 2014 at 1:35 PM
Message: 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.

Subject: Great stuff
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 20, 2014 at 1:35 PM
Message: Love your server names...
Great tool in the belt.

Subject: Re: not nearly as fancy
Posted by: Phil Factor (view profile)
Posted on: Thursday, August 21, 2014 at 3:03 PM
Message: gosh, coincidence. That's the way I used to have to do it!

Subject: Re: great stuff.
Posted by: Phil Factor (view profile)
Posted on: Thursday, August 21, 2014 at 3:10 PM
Message: 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.

Subject: Wow!!
Posted by: cmkony04 (view profile)
Posted on: Monday, September 29, 2014 at 8:30 PM
Message: 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

Thanks

Subject: Re: Wow.
Posted by: Phil Factor (view profile)
Posted on: Tuesday, September 30, 2014 at 9:45 AM
Message: 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!

Subject: Thank you
Posted by: cmkony04 (view profile)
Posted on: Thursday, October 2, 2014 at 10:11 AM
Message: Thank you for your assistance.

 
Simple-Talk Database Delivery

DLM
Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Schema-Based Access Control for SQL Server Databases

Access-control within the database is important for the security of data, but it should be simple to implement. It... Read more...

 View the blog

Top Rated

Understanding Cross-Database Transactions in SQL Server
 Microsoft 'Always On' technology does not support distributed or cross-database transactions. Why not?... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Archiving Hierarchical, Deleted Transactions Using XML
 When you delete a business transaction from the database, there are times when you might want to keep a... Read more...

The Mindset of the Enterprise DBA: Harnessing the Power of Automation
 After you have done the necessary groundwork of standardizing and centralizing your database... Read more...

Rollback and Recovery Troubleshooting; Challenges and Strategies
 What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.