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!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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.
|
1 2 3 4 |
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…
|
1 |
Select * from Adventureworks.dbo.AWBuildVersion for xml path |
And you get…
|
1 2 |
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.
|
1 2 |
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 …
|
1 2 3 4 5 6 |
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…
|
1 |
Select * from person.address for XML path |
and you get ….
|
1 2 |
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 …
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 |
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'),'<','<'),'>','>')+'</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'),'<','<'),'>','>')+'</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'),'<','<'),'>','>')+'</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
|
1 |
$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)
|
1 2 3 |
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 …
|
1 |
$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 …
|
1 |
-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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 |
. ".\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'),'<','<'),'>','>')+'</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'),'<','<'),'>','>')+'</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'),'<','<'),'>','>')+'</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.
Load comments