{"id":1852,"date":"2014-08-12T00:00:00","date_gmt":"2014-08-12T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/quickly-investigating-whats-in-the-tables-of-sql-server-databases\/"},"modified":"2021-09-29T16:21:34","modified_gmt":"2021-09-29T16:21:34","slug":"quickly-investigating-whats-in-the-tables-of-sql-server-databases","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/quickly-investigating-whats-in-the-tables-of-sql-server-databases\/","title":{"rendered":"Quickly Investigating What’s in the Tables of SQL Server Databases"},"content":{"rendered":"
\n

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.<\/p>\n

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<\/a>, and Adam Machanic’s sp_whoisactive<\/code>: 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.<\/p>\n

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!)<\/p>\n

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.<\/p>\n

The Beef<\/h1>\n

\u00a0Here is the old PUBS<\/code> 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!<\/p>\n

\n
\n
    \n
  1. [dbo].[authors]<\/a><\/li>\n
  2. [dbo].[discounts]<\/a><\/li>\n
  3. [dbo].[employee]<\/a><\/li>\n
  4. [dbo].[jobs]<\/a><\/li>\n
  5. [dbo].[pub_info]<\/a><\/li>\n
  6. [dbo].[publishers]<\/a><\/li>\n
  7. [dbo].[roysched]<\/a><\/li>\n
  8. [dbo].[sales]<\/a><\/li>\n
  9. [dbo].[stores]<\/a><\/li>\n
  10. [dbo].[titleauthor]<\/a><\/li>\n
  11. [dbo].[titles]<\/a><\/li>\n<\/ol>\n<\/div>\n\n\n\n\n\n\n\n
    au_id<\/th>\nau_lname<\/th>\nau_fname<\/th>\nphone<\/th>\naddress<\/th>\ncity<\/th>\nstate<\/th>\nzip<\/th>\ncontract<\/th>\n<\/tr>\n<\/thead>\n
    172-32-1176<\/td>\nWhite<\/td>\nJohnson<\/td>\n408 496-7223<\/td>\n10932 Bigge Rd.<\/td>\nMenlo Park<\/td>\nCA<\/td>\n94025<\/td>\n1<\/td>\n<\/tr>\n
    213-46-8915<\/td>\nGreen<\/td>\nMarjorie<\/td>\n415 986-7020<\/td>\n309 63rd St. #411<\/td>\nOakland<\/td>\nCA<\/td>\n94618<\/td>\n1<\/td>\n<\/tr>\n
    238-95-7766<\/td>\nCarson<\/td>\nCheryl<\/td>\n415 548-7723<\/td>\n589 Darwin Ln.<\/td>\nBerkeley<\/td>\nCA<\/td>\n94705<\/td>\n1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n
    discounttype<\/th>\nstor_id<\/th>\nlowqty<\/th>\nhighqty<\/th>\ndiscount<\/th>\n<\/tr>\n<\/thead>\n
    Initial Customer<\/td>\nNULL<\/td>\nNULL<\/td>\nNULL<\/td>\n10.50<\/td>\n<\/tr>\n
    Volume Discount<\/td>\nNULL<\/td>\n100<\/td>\n1000<\/td>\n6.70<\/td>\n<\/tr>\n
    Customer Discount<\/td>\n8042<\/td>\nNULL<\/td>\nNULL<\/td>\n5.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n
    emp_id<\/th>\nfname<\/th>\nminit<\/th>\nlname<\/th>\njob_id<\/th>\njob_lvl<\/th>\npub_id<\/th>\nhire_date<\/th>\n<\/tr>\n<\/thead>\n
    PMA42628M<\/td>\nPaolo<\/td>\nM<\/td>\nAccorti<\/td>\n13<\/td>\n35<\/td>\n0877<\/td>\n1992-08-27T00:00:00<\/td>\n<\/tr>\n
    PSA89086M<\/td>\nPedro<\/td>\nS<\/td>\nAfonso<\/td>\n14<\/td>\n89<\/td>\n1389<\/td>\n1990-12-24T00:00:00<\/td>\n<\/tr>\n
    VPA30890F<\/td>\nVictoria<\/td>\nP<\/td>\nAshworth<\/td>\n6<\/td>\n140<\/td>\n0877<\/td>\n1990-09-13T00:00:00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n
    job_id<\/th>\njob_desc<\/th>\nmin_lvl<\/th>\nmax_lvl<\/th>\n<\/tr>\n<\/thead>\n
    1<\/td>\nNew Hire – Job not specified<\/td>\n10<\/td>\n10<\/td>\n<\/tr>\n
    2<\/td>\nChief Executive Officer<\/td>\n200<\/td>\n250<\/td>\n<\/tr>\n
    3<\/td>\nBusiness Operations Manager<\/td>\n175<\/td>\n225<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n
    pub_id<\/th>\nlogo<\/th>\npr_info<\/th>\n<\/tr>\n<\/thead>\n
    0736<\/td>\nR0lGODlh0wAfALMPAAAAAIAAAACAAICAAAAAgIAAg<\/td>\nThis is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is<\/td>\n<\/tr>\n
    0877<\/td>\nR0lGODlhiwAvALMPAAAAAIAAAACAAICAAAAAgIAAg<\/td>\nThis is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley<\/td>\n<\/tr>\n
    1389<\/td>\nR0lGODlhwgAdALMPAAAAAIAAAACAAICAAAAAg<\/td>\nThis is sample text data for Algodata Infosystems, publisher 1389 in the pubs database. Algodata Inf<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n
    pub_id<\/th>\npub_name<\/th>\ncity<\/th>\nstate<\/th>\ncountry<\/th>\n<\/tr>\n<\/thead>\n
    0736<\/td>\nNew Moon Books<\/td>\nBoston<\/td>\nMA<\/td>\nUSA<\/td>\n<\/tr>\n
    0877<\/td>\nBinnet & Hardley<\/td>\nWashington<\/td>\nDC<\/td>\nUSA<\/td>\n<\/tr>\n
    1389<\/td>\nAlgodata Infosystems<\/td>\nBerkeley<\/td>\nCA<\/td>\nUSA<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n
    title_id<\/th>\nlorange<\/th>\nhirange<\/th>\nroyalty<\/th>\n<\/tr>\n<\/thead>\n
    BU1032<\/td>\n0<\/td>\n5000<\/td>\n10<\/td>\n<\/tr>\n
    BU1032<\/td>\n5001<\/td>\n50000<\/td>\n12<\/td>\n<\/tr>\n
    PC1035<\/td>\n0<\/td>\n2000<\/td>\n10<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n
    stor_id<\/th>\nord_num<\/th>\nord_date<\/th>\nqty<\/th>\npayterms<\/th>\ntitle_id<\/th>\n<\/tr>\n<\/thead>\n
    6380<\/td>\n6871<\/td>\n1994-09-14T00:00:00<\/td>\n5<\/td>\nNet 60<\/td>\nBU1032<\/td>\n<\/tr>\n
    6380<\/td>\n722a<\/td>\n1994-09-13T00:00:00<\/td>\n3<\/td>\nNet 60<\/td>\nPS2091<\/td>\n<\/tr>\n
    7066<\/td>\nA2976<\/td>\n1993-05-24T00:00:00<\/td>\n50<\/td>\nNet 30<\/td>\nPC8888<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n
    stor_id<\/th>\nstor_name<\/th>\nstor_address<\/th>\ncity<\/th>\nstate<\/th>\nzip<\/th>\n<\/tr>\n<\/thead>\n
    6380<\/td>\nEric the Read Books<\/td>\n788 Catamaugus Ave.<\/td>\nSeattle<\/td>\nWA<\/td>\n98056<\/td>\n<\/tr>\n
    7066<\/td>\nBarnum’s<\/td>\n567 Pasadena Ave.<\/td>\nTustin<\/td>\nCA<\/td>\n92789<\/td>\n<\/tr>\n
    7067<\/td>\nNews & Brews<\/td>\n577 First St.<\/td>\nLos Gatos<\/td>\nCA<\/td>\n96745<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n
    au_id<\/th>\ntitle_id<\/th>\nau_ord<\/th>\nroyaltyper<\/th>\n<\/tr>\n<\/thead>\n
    172-32-1176<\/td>\nPS3333<\/td>\n1<\/td>\n100<\/td>\n<\/tr>\n
    213-46-8915<\/td>\nBU1032<\/td>\n2<\/td>\n40<\/td>\n<\/tr>\n
    213-46-8915<\/td>\nBU2075<\/td>\n1<\/td>\n100<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n
    title_id<\/th>\ntitle<\/th>\ntype<\/th>\npub_id<\/th>\nprice<\/th>\nadvance<\/th>\nroyalty<\/th>\nytd_sales<\/th>\nnotes<\/th>\npubdate<\/th>\n<\/tr>\n<\/thead>\n
    BU1032<\/td>\nThe Busy Executive’s Database Guide<\/td>\nbusiness<\/td>\n1389<\/td>\n19.9900<\/td>\n5000.0000<\/td>\n10<\/td>\n4095<\/td>\nAn overview of available database systems with emphasis on common business applications. Illustrated<\/td>\n1991-06-12T00:00:00<\/td>\n<\/tr>\n
    BU1111<\/td>\nCooking with Computers: Surreptitious Balance Sheets<\/td>\nbusiness<\/td>\n1389<\/td>\n11.9500<\/td>\n5000.0000<\/td>\n10<\/td>\n3876<\/td>\nHelpful hints on how to use your electronic resources to the best advantage.<\/td>\n1991-06-09T00:00:00<\/td>\n<\/tr>\n
    BU2075<\/td>\nYou Can Combat Computer Stress!<\/td>\nbusiness<\/td>\n0736<\/td>\n2.9900<\/td>\n10125.0000<\/td>\n24<\/td>\n18722<\/td>\nThe latest medical and psychological techniques for living with the electronic office. Easy-to-under<\/td>\n1991-06-30T00:00:00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    Here, in a separate HTML page is AdventureWorks2012<\/a>, done the same way.<\/p>\n

    The start of the page looks a bit like this (seen from a distance)<\/p>\n

    \"2033-Adventureworks.jpg\"<\/a><\/p>\n

    The tables from adventureWorks, seen from a browser (thumbnail)<\/p>\n

    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.<\/p>\n

    Selecting tables<\/h1>\n

    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<\/code>!<\/p>\n

    DECLARE @WildCardName VARCHAR(150) --the wildcard to represent the tables you want\r\nDECLARE @tablesToDo TABLE(TheOrder INT IDENTITY,TheTable VARCHAR(2000))\r\n\u00a0\u00a0\u00a0 --this is the table variable containing our list of tables\r\n\u00a0\r\nSELECT @WildCardName ='%.%';\r\n\u00a0\r\n\u00a0\u00a0\u00a0 --if no Schema was specified, we'll assume all schemas are intended\r\nIF PARSENAME(@WildCardName,2) IS NULL \r\n\u00a0\u00a0\u00a0 SELECT @WildCardName='%.'+@WildCardName;\r\n\u00a0\u00a0\u00a0 --now we fetch all the table names into the table\r\nINSERT INTO @tablesToDo (TheTable) --insert the names in order into the table\r\n\u00a0\u00a0\u00a0 SELECT QUOTENAME(schema_name([schema_ID]))+'.'+QUOTENAME(name) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM sys.tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE name LIKE PARSENAME(@WildCardName,1)--the table name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND schema_name([schema_ID]) LIKE PARSENAME(@WildCardName,2)--the schema\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY schema_name([schema_ID]),name; --order by schema, followed by name \r\n\u00a0\u00a0\u00a0 --nothing found? We warn the user and abort.\r\nIF @@rowcount=0 --if we found nothing\r\n\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0 RAISERROR\u00a0 ('<p>No such table like ''%s'' in this database<\/p>',16,1,@WildCardName);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --return 1\r\n\u00a0\u00a0\u00a0 END\r\n<\/pre>\n
    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 <\/code>statements for tables.<\/p>\n
    Displaying the list of tables.<\/h2>\n
    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.\u00a0 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.<\/p>\n
    Declare @Contents Varchar(MAX)\r\nSelect @Contents=coalesce(@Contents,'')+'<li><a href=\"#table'+convert(Varchar(5),TheOrder)+'\">'+TheTable+'<\/a><\/li>\r\n'\u00a0\u00a0 FROM @tablesToDo\r\nSelect @contents='<ol>'+@contents+'<\/ol>' \r\n<\/pre>\n
    Displaying the contents of the first three rows<\/h2>\n
    You just need to execute\u00a0 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>\u00a0 FOR XML PATH<\/code><\/p>\n
    Illegal column names<\/h3>\n
    If it comes across a column name that is illegal in XML terms, it throws its hands up and errors out.<\/p>\n
    Try, for example…<\/p>\n
    Select * from Adventureworks.dbo.AWBuildVersion for xml path\r\n<\/pre>\n
    And you get…<\/p>\n
    Msg 6850, Level 16, State 1, Line 1 \r\nColumn name 'Database Version' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault.\r\n<\/pre>\n
    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.<\/p>\n
    Select SystemInformationID, [Database Version] as DatabaseVersion, VersionDate, ModifiedDate \r\nfrom Adventureworks.dbo.AWBuildVersion for xml path\r\n<\/pre>\n
    However, you still get automatic truncation and conversion of the values, which is handy.<\/p>\n
    So doing this …<\/p>\n
    DECLARE @XML XML;\r\nSelect @XML=(Select SystemInformationID, [Database Version] as DatabaseVersion, VersionDate, ModifiedDate \r\nfrom Adventureworks.dbo.AWBuildVersion for xml path, ELEMENTS XSINIL, root);\r\nSELECT\u00a0 [x].value('local-name(.)', 'varchar(100)'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [x].value('text()[1]','varchar(100)')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 FROM @XML.nodes('root\/row[1]\/*')\u00a0 as a(x);\r\n<\/pre>\n
    … will give you this:<\/p>\n\n\n\n\n\n\n\n
    \n
    Column_Name<\/strong><\/p>\n<\/td>\n
    		\n
    The_Value<\/strong><\/p>\n<\/td>\n<\/tr>\n
    \n
    SystemInformationID<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    DatabaseVersion<\/p>\n<\/td>\n
    		\n
    10.00.80404.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    VersionDate<\/p>\n<\/td>\n
    		\n
    2008-04-04T00:00:00<\/p>\n<\/td>\n<\/tr>\n
    \n
    ModifiedDate<\/p>\n<\/td>\n
    		\n
    2008-04-04T00:00:00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    … and so it is just a matter of getting the next two row values and formatting it all up in an HTML table.<\/p>\n
    CLR Datatypes<\/h3>\n
    One other problem you’ll hit is that The FOR XML <\/code>phrase will cause SELECT * FROM \t<\/code>to crash if there is a CLR data type in the table. Try, for example…<\/p>\n
    \tSelect * from person.address for XML path<\/pre>\n
    and you get ….<\/p>\n
    Msg 6865, Level 16, State 1, Line 2\r\n \tFOR XML does not support CLR types - cast CLR types explicitly into one of the supported types in FOR XML queries.<\/pre>\n
    so instead, you need to\u00a0 do …<\/p>\n
    Select AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode, convert(varchar(100),SpatialLocation), rowguid, ModifiedDate from person.address for XML path<\/pre>\n
    ..and by doing an explicit conversion, everything works happily.<\/p>\n
    NULLs<\/h3>\n
    You’ll notice that we’ve specified ELEMENTS XSINIL, which gives us NULL<\/code> values. Unless you do this, XML assumes that the column doesn’t exist for that row, which is pretty reasonable\u00a0 for a data document. However, it isn’t correct for a SQL table.<\/p>\n
    Putting it all together<\/h2>\n
    The devil is in the detail, as any SQL Developer will tell you.<\/p>\n
    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<\/a>, published elsewhere<\/a>, called  spSaveTextToFile<\/a> 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 <\/code>enabled on the server.<\/p>\n
    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<\/code> to 'd:\\files\\' <\/code>(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<\/code> to something suitable!<\/p>\n
    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<\/a>.<\/p>\n
    \tSet nocount on \r\n\tSET ARITHABORT ON\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\tDECLARE @WildCardName VARCHAR(150) --the wildcard to represent the tables you want\r\n\tDECLARE @Directory VARCHAR(255)--the full path of the directory where you want to store the file \r\n\t\u00a0\u00a0\u00a0 --this is the table variable containing our list of tables\r\n\tSELECT @WildCardName ='%.%', @Directory='d:\\files\\'\r\n\tDECLARE @FileNameAndPath VARCHAR(255) --the path and the file \r\n\tDECLARE @ColumnList NVARCHAR(MAX) --comma delimited list of columns\r\n\tDECLARE @x XML, @HTML VARCHAR(MAX), @Row VARCHAR(MAX), @CrLf CHAR(2)\r\n\tDECLARE @Contents NVARCHAR(MAX)\r\n\tDECLARE @Errors NVARCHAR(MAX)\r\n\tDECLARE @SQL NVARCHAR(MAX) --the dynamic SQL that we create\r\n\tDECLARE @ii INT ,@iiMax INT --the counters for our loop\r\n\tDECLARE @TheTable VARCHAR(2000) --the name of the table being documented\r\n\tDECLARE @tablesToDo TABLE(TheOrder INT IDENTITY,TheTable VARCHAR(2000))\r\n\t\u00a0\u00a0\u00a0 --if no Schema was specified, we'll assume all schemas are intended\r\n\tDECLARE @columnsToDo TABLE (FirstBadCharacter int ,name varchar(255), RedactedName varchar(255), column_ID int, Is_Assembly_Type bit )\r\n\t\r\n\tIF PARSENAME(@WildCardName,2) IS NULL \r\n\t\u00a0\u00a0\u00a0 SELECT @WildCardName='%.'+@WildCardName;\r\n\t\u00a0\u00a0\u00a0 --now we fetch all the table names into the table\r\n\tINSERT INTO @tablesToDo (TheTable) --insert the names in order into the table\r\n\t\u00a0\u00a0\u00a0 SELECT QUOTENAME(schema_name([schema_ID]))+'.'+QUOTENAME(name) \r\n\t\u00a0\u00a0\u00a0 FROM sys.tables\r\n\t\u00a0\u00a0\u00a0 WHERE name LIKE PARSENAME(@WildCardName,1)--the table name\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND schema_name([schema_ID]) LIKE PARSENAME(@WildCardName,2)--the schema\r\n\t\u00a0\u00a0\u00a0 ORDER BY schema_name([schema_ID]),name; --order by schema, followed by name\u00a0\u00a0 \r\n\t\u00a0\u00a0\u00a0 --nothing found? We warn the user and abort.\r\n\tIF @@rowcount=0 --if we found nothing\r\n\t\u00a0\u00a0\u00a0 BEGIN\r\n\t\u00a0\u00a0\u00a0 Select @errors='<H4>No such table like '''+@WildCardName+''' in this database<\/H4>';\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --return 1\r\n\t\u00a0\u00a0\u00a0 END\r\n\t\u00a0\u00a0\u00a0 --and we loop through each table, creating the HTML table that shows the first three rows.\r\n\tSELECT\u00a0 @CrLf=CHAR(13)+CHAR(10), @Contents='', @HTML='', @ii=1,@iiMax=MAX(TheOrder) \r\n\t\u00a0\u00a0\u00a0 FROM @tablesToDo;\r\n\tWHILE @ii<=@iiMax\r\n\t\u00a0\u00a0\u00a0 BEGIN\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --get the name and schema of the next table to do\r\n\t\u00a0\u00a0\u00a0 SELECT @TheTable=TheTable,@ii=@ii+1 \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @tablesToDo \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE TheOrder=@ii;\r\n\t\u00a0\u00a0\u00a0 SELECT @Contents=@Contents+'<li><a href=\"#table'+CONVERT(VARCHAR(5),@ii)+'\">'+@TheTable+'<\/a><\/li>'+@CrLf;\r\n\t\u00a0\u00a0\u00a0 \/* get the name of the column and take out any problem characters for XML and HTML *\/\r\n\t\u00a0\u00a0\u00a0 Delete from @columnsToDo\u00a0 \r\n\t\u00a0\u00a0\u00a0 insert into @columnsToDo (FirstBadCharacter,name,RedactedName,Column_ID,is_assembly_type)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sELECT Patindex('%[^a-zA-Z_0-9]%',sys.columns.NAME COLLATE Latin1_General_CI_AI),sys.columns.Name,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sys.columns.Name,Column_ID,is_assembly_type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 FROM sys.columns \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 inner join sys.types \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on sys.columns.user_type_id =sys.types.user_type_id\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE OBJECT_NAME([object_id]) LIKE PARSENAME(@TheTable,1)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND object_schema_name([object_ID]) LIKE PARSENAME(@TheTable,2)\r\n\t\u00a0\u00a0\u00a0 while exists (Select * from @columnsToDo where FirstBadCharacter>0)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Begin\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 update @columnsToDo\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Set RedactedName= stuff(RedactedName,FirstBadCharacter,1,'_') where FirstBadCharacter>0\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 update @columnsToDo\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Set FirstBadCharacter=Patindex('%[^a-zA-Z_0-9]%',RedactedName COLLATE Latin1_General_CI_AI)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 where FirstBadCharacter>0\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 end\r\n\t\u00a0\u00a0\u00a0 SELECT @ColumnList=STUFF((\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT ','+case when is_assembly_type=1 then 'CONVERT(VARCHAR(2000),['+name+']) AS ['+RedactedName+'] '\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else '['+name+'] AS ['+RedactedName+'] ' end\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @columnsToDo\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY column_ID\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML PATH (''), TYPE).value('.', 'varchar(max)') ,1,1,'');\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --get the top three rows (meaningless as we haven't specified the order) as XML\r\n\t\u00a0\u00a0\u00a0 SELECT @SQL=N'Select @TheXML=((Select top 3 '+@columnList+' from '+@TheTable\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +' for XML path, ELEMENTS XSINIL, root))'\r\n\t\u00a0\u00a0\u00a0 EXECUTE sp_ExecuteSQL\u00a0 @statement =\u00a0 @SQL,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @params = N'@TheXML XML\u00a0 OUTPUT',\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @TheXML = @x output\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --now we do the TABLE tag in HTML with the name of the table and the caption\r\n\t\u00a0\u00a0\u00a0 SELECT @HTML=@HTML+'<table id=\"table\"\"'+CONVERT(VARCHAR(5),@ii)+'\" class=\"tablecontents\" border=\"1\" \r\n\tsummary=\"first three rows in table'''+@TheTable+'''\">\r\n\t<caption>'+@TheTable+'<\/caption>\r\n\t<thead>\r\n\t<tr>\r\n\t',@Row=''\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --if no data there then we just give the column names taken from the system tables.\r\n\t\u00a0\u00a0\u00a0 IF @X IS NULL --no XML output from the executed batch\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN --just adding in all the column names in the header\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @HTML=@html+'<th>'+name+'<\/th>'+@CrLf \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @columnsToDo\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY column_ID\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @HTML=@HTML+'<\/tr><\/thead>'\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\t\u00a0\u00a0\u00a0 ELSE--it was valid XML result so there was data.\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --get the heading line for the column names\u00a0 \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @HTML=@HTML+'<th>'+[x].value('local-name(.)', 'varchar(100)')+'<\/th>',--+@CrLf,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Row=@row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'<\/td>'--+@CrLf\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @x.nodes('root\/row[1]\/*')\u00a0 AS a(x)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --and add it to the table\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @html=@HTML+'<\/tr><\/thead>'+@CrLf+'<tbody><tr>'+@Row+'<\/tr>'+@CrLf, @row=''\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --now we collect the data from\u00a0 any second row\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @Row=@Row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'<\/td>'--+@CrLf\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @x.nodes('root\/row[2]\/*')\u00a0 AS a(x)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --if there was a second row we add it.\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @@Rowcount>0\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @html=@HTML+'<tr>'+@Row+'<\/tr>'+@CrLf, @row=''\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --now we get the third row if there is one\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @Row=@Row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'<\/td>'-- +@CrLf\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @x.nodes('root\/row[3]\/*')\u00a0 AS a(x)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @@Rowcount>0\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @html=@HTML+@CrLf+'<tr>'+@Row+'<\/tr>',@row=''\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\t\u00a0\u00a0\u00a0 SELECT @html=@HTML+'<\/tbody><\/table>'\r\n\t\u00a0\u00a0\u00a0 END\r\n\t\/* 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 *\/\u00a0\u00a0\u00a0 \r\n\tSELECT @HTML='<!DOCTYPE html>\r\n\t<html>\r\n\t<head>\r\n\t<title>'+@@Servername+'-' +DB_NAME()+'<\/title>\r\n\t<\/head>\r\n\t<style>\r\n\t<!--\r\n\t\r\n\t.columnar { columns: 4; -moz-column-width: 15em; -webkit-column-width:15em; column-width: 15em; }\r\n\t\r\n\t.thetables { }\r\n\t\r\n\t\/* do the basic style for the entire table *\/\r\n\t.thetables table {\r\n\t\u00a0\u00a0 border-collapse: collapse;\r\n\t\u00a0\u00a0 border: none ;\r\n\t\u00a0\u00a0 font: 11px Verdana, Geneva, Arial, Helvetica, sans-serif;\r\n\t\u00a0\u00a0 color: black;\r\n\t\u00a0\u00a0 margin-left:20px;\r\n\t\u00a0\u00a0 margin-top: 20px;\r\n\t\r\n\t\u00a0\u00a0\u00a0 }\r\n\t\/*attach the styles to the caption of the table *\/\r\n\t.thetables table caption {\r\n\t\u00a0 font-weight: bold;\r\n\t\u00a0 text-align:left;\r\n\t\u00a0 padding-left:5px;\r\n\t\u00a0 background-color: #f3f3f3;\r\n\t}\r\n\t\u00a0\u00a0 \r\n\t\/*give every cell the same style of border *\/\r\n\t.thetables table td, .thetables table th, .thetables table caption { border: 1px solid #bbbde1\u00a0 ;\u00a0 vertical-align: top;\u00a0 }\r\n\t\/* apply styles to the odd headers *\/\r\n\t.thetables table th:nth-child(odd) { background-color: #cedfe2; }\r\n\t\/* apply styles to the even headers *\/\r\n\t.thetables table tr th:nth-child(even) { background-color: #dfebee; }\r\n\t\u00a0\/* apply styles to the even rows *\/\r\n\t.thetables table td {background-color: #f0f7f9;}\r\n\t\r\n\t.thetables table tr:nth-child(even) td:nth-child(odd){background-color: #f7fafb;\u00a0 }\r\n\t\u00a0\/* apply styles to the even colums of odd rows *\/\r\n\t.thetables table tr:nth-child(odd) td:nth-child(even){ background-color: #f7fafb; }\r\n\t\r\n\th1, ol { color: #000000; text-align: left; font: normal 11px Verdana, Geneva, Arial, Helvetica, sans-serif; }\r\n\t\r\n\th1 { font-size: 16px; font-weight: bold; color: #000000; text-align: left; }\r\n\t-->\r\n\t<\/style>\r\n\t\r\n\t<body>\r\n\t<h1>Sample of contents of tables in '+@@Servername+'-'+DB_NAME()+'<\/h1>\r\n\t<div class=\"columnar\"><ol>'+@Contents+'<\/ol><\/div>\r\n\t<div class=\"thetables\">\u00a0\u00a0\u00a0 \r\n\t'\u00a0\u00a0\u00a0 +Coalesce(@HTML,'')+coalesce(@errors,'')+'\r\n\t<\/div>\r\n\t<\/body>\r\n\t<\/html>'\r\n\t\/* and now we write out the result *\/\r\n\tSELECT @FileNameAndPath=@Directory+REPLACE(REPLACE(REPLACE(@@Servername+'-'+DB_NAME(),'\/',''),'\\',''),':','')+'.html'\r\n\tEXECUTE philfactor.dbo.spSaveTextToFile @html,@FileNameAndPath\u00a0 \r\n\t\u00a0\r\n<\/pre>\n
    Putting It In PowerShell<\/h2>\n
    I us a PowerShell routine that uses the function 'Foreach-DatabaseInServers<\/code>‘ that I published a couple of years ago here ‘PowerShell SMO: Just Writing Things Once’<\/a>. 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.<\/p>\n
    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.<\/p>\n
    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.<\/p>\n
    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.<\/p>\n
    Getting it running<\/h2>\n
    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<\/p>\n
    $servers=@('Shem','Ham','Japeth')#Specify the list of servers you want to use<\/pre>\n
    to something else.<\/p>\n
    I always use my Central Management Server groups<\/a>, rather than do lots of handmade lists of my servers (fortunately they are all within the domain so I can use Windows Security)<\/p>\n
    Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers\r\n#get a list of the servers we want to scan\r\n$servers= dir 'SQLSERVER:\\sqlregistration\\Database Engine Server Group' | foreach-object{$_.name}\r\n\r\n<\/pre>\n
    You will also need to change the …<\/p>\n
    $LocalDirectory='E:\\MyDatabaseTables\\'#the path to the local directory where you want to store it<\/pre>\n
    … to point to a valid directory. You might\u00a0 also want to change or remove the database filter …<\/p>\n
    -TheDatabaseFilter\u00a0 {param($x); if ($x.name -like'*'){$x}}<\/pre>\n
    … If\u00a0 you only want to list out Adventureworks databases it would be ‘ADV*’. ( you can use this to exclude databases)<\/p>\n
    \t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\r\n\t$servers=@('Shem','Ham','Japeth') #Specify the list of servers you want to use\r\n\t$TableWildcard='%.%'#Which schema and tables do you want to do (%.% means all tables from all schema\r\n\t$LocalDirectory='E:\\Tables\\'#the path to the local directory where you want to store it\r\n\t$SQL=@\"\r\n\tSet nocount on\r\n\tSET ARITHABORT ON\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\tDECLARE @WildCardName VARCHAR(150) --the wildcard to represent the tables you want\r\n\tDECLARE @Directory VARCHAR(255)--the full path of the directory where you want to store the file \r\n\t\u00a0\u00a0\u00a0 --this is the table variable containing our list of tables\r\n\tSELECT @WildCardName ='$TableWildcard', @Directory='$LocalDirectory'\r\n\tDECLARE @FileNameAndPath VARCHAR(255) --the path and the file \r\n\tDECLARE @ColumnList NVARCHAR(MAX) --comma delimited list of columns\r\n\tDECLARE @x XML, @HTML VARCHAR(MAX), @Row VARCHAR(MAX), @CrLf CHAR(2)\r\n\tDECLARE @Contents NVARCHAR(MAX)\r\n\tDECLARE @Errors NVARCHAR(MAX)\r\n\tDECLARE @SQL NVARCHAR(MAX) --the dynamic SQL that we create\r\n\tDECLARE @ii INT ,@iiMax INT --the counters for our loop\r\n\tDECLARE @TheTable VARCHAR(2000) --the name of the table being documented\r\n\tDECLARE @tablesToDo TABLE(TheOrder INT IDENTITY,TheTable VARCHAR(2000))\r\n\t\u00a0\u00a0\u00a0 --if no Schema was specified, we'll assume all schemas are intended\r\n\tDECLARE @columnsToDo TABLE (FirstBadCharacter int ,name varchar(255), RedactedName varchar(255), column_ID int, Is_Assembly_Type bit )\r\n\t\r\n\tIF PARSENAME(@WildCardName,2) IS NULL \r\n\t\u00a0\u00a0\u00a0 SELECT @WildCardName='%.'+@WildCardName;\r\n\t\u00a0\u00a0\u00a0 --now we fetch all the table names into the table\r\n\tINSERT INTO @tablesToDo (TheTable) --insert the names in order into the table\r\n\t\u00a0\u00a0\u00a0 SELECT QUOTENAME(schema_name([schema_ID]))+'.'+QUOTENAME(name) \r\n\t\u00a0\u00a0\u00a0 FROM sys.tables\r\n\t\u00a0\u00a0\u00a0 WHERE name LIKE PARSENAME(@WildCardName,1)--the table name\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND schema_name([schema_ID]) LIKE PARSENAME(@WildCardName,2)--the schema\r\n\t\u00a0\u00a0\u00a0 ORDER BY schema_name([schema_ID]),name; --order by schema, followed by name\u00a0\u00a0 \r\n\t\u00a0\u00a0\u00a0 --nothing found? We warn the user and abort.\r\n\tIF @@rowcount=0 --if we found nothing\r\n\t\u00a0\u00a0\u00a0 BEGIN\r\n\t\u00a0\u00a0\u00a0 Select @errors='<H4>No such table like '''+@WildCardName+''' in this database<\/H4>';\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --return 1\r\n\t\u00a0\u00a0\u00a0 END\r\n\t\u00a0\u00a0\u00a0 --and we loop through each table, creating the HTML table that shows the first three rows.\r\n\tSELECT\u00a0 @CrLf=CHAR(13)+CHAR(10), @Contents='', @HTML='', @ii=1,@iiMax=MAX(TheOrder) \r\n\t\u00a0\u00a0\u00a0 FROM @tablesToDo;\r\n\tWHILE @ii<=@iiMax\r\n\t\u00a0\u00a0\u00a0 BEGIN\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --get the name and schema of the next table to do\r\n\t\u00a0\u00a0\u00a0 SELECT @TheTable=TheTable,@ii=@ii+1 \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @tablesToDo \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE TheOrder=@ii;\r\n\t\u00a0\u00a0\u00a0 SELECT @Contents=@Contents+'<li><a href=\"#table'+CONVERT(VARCHAR(5),@ii)+'\">'+@TheTable+'<\/a><\/li>'+@CrLf;\r\n\t\u00a0\u00a0\u00a0 \/* get the name of the column and take out any problem characters for XML and HTML *\/\r\n\t\u00a0\u00a0\u00a0 Delete from @columnsToDo\u00a0 \r\n\t\u00a0\u00a0\u00a0 insert into @columnsToDo (FirstBadCharacter,name,RedactedName,Column_ID,is_assembly_type)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sELECT Patindex('%[^a-zA-Z_0-9]%',sys.columns.NAME COLLATE Latin1_General_CI_AI),sys.columns.Name,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sys.columns.Name,Column_ID,is_assembly_type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 FROM sys.columns \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 inner join sys.types \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on sys.columns.user_type_id =sys.types.user_type_id\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE OBJECT_NAME([object_id]) LIKE PARSENAME(@TheTable,1)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND object_schema_name([object_ID]) LIKE PARSENAME(@TheTable,2)\r\n\t\u00a0\u00a0\u00a0 while exists (Select * from @columnsToDo where FirstBadCharacter>0)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Begin\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 update @columnsToDo\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Set RedactedName= stuff(RedactedName,FirstBadCharacter,1,'_') where FirstBadCharacter>0\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 update @columnsToDo\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Set FirstBadCharacter=Patindex('%[^a-zA-Z_0-9]%',RedactedName COLLATE Latin1_General_CI_AI)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 where FirstBadCharacter>0\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 end\r\n\t\u00a0\u00a0\u00a0 SELECT @ColumnList=STUFF((\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT ','+case when is_assembly_type=1 then 'CONVERT(VARCHAR(2000),['+name+']) AS ['+RedactedName+'] '\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else '['+name+'] AS ['+RedactedName+'] ' end\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @columnsToDo\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY column_ID\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML PATH (''), TYPE).value('.', 'varchar(max)') ,1,1,'');\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --get the top three rows (meaningless as we haven't specified the order) as XML\r\n\t\u00a0\u00a0\u00a0 SELECT @SQL=N'Select @TheXML=((Select top 3 '+@columnList+' from '+@TheTable\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +' for XML path, ELEMENTS XSINIL, root))'\r\n\t\u00a0\u00a0\u00a0 EXECUTE sp_ExecuteSQL\u00a0 @statement =\u00a0 @SQL,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @params = N'@TheXML XML\u00a0 OUTPUT',\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @TheXML = @x output\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --now we do the TABLE tag in HTML with the name of the table and the caption\r\n\t\u00a0\u00a0\u00a0 SELECT @HTML=@HTML+'<table id=\"table\"\"'+CONVERT(VARCHAR(5),@ii)+'\" class=\"tablecontents\" border=\"1\" \r\n\tsummary=\"first three rows in table'''+@TheTable+'''\">\r\n\t<caption>'+@TheTable+'<\/caption>\r\n\t<thead>\r\n\t<tr>\r\n\t',@Row=''\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --if no data there then we just give the column names taken from the system tables.\r\n\t\u00a0\u00a0\u00a0 IF @X IS NULL --no XML output from the executed batch\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN --just adding in all the column names in the header\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @HTML=@html+'<th>'+name+'<\/th>'+@CrLf \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @columnsToDo\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY column_ID\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @HTML=@HTML+'<\/tr><\/thead>'\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\t\u00a0\u00a0\u00a0 ELSE--it was valid XML result so there was data.\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --get the heading line for the column names\u00a0 \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @HTML=@HTML+'<th>'+[x].value('local-name(.)', 'varchar(100)')+'<\/th>',--+@CrLf,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Row=@row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'<\/td>'--+@CrLf\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @x.nodes('root\/row[1]\/*')\u00a0 AS a(x)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --and add it to the table\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @html=@HTML+'<\/tr><\/thead>'+@CrLf+'<tbody><tr>'+@Row+'<\/tr>'+@CrLf, @row=''\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --now we collect the data from\u00a0 any second row\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @Row=@Row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'<\/td>'--+@CrLf\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @x.nodes('root\/row[2]\/*')\u00a0 AS a(x)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --if there was a second row we add it.\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @@Rowcount>0\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @html=@HTML+'<tr>'+@Row+'<\/tr>'+@CrLf, @row=''\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --now we get the third row if there is one\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @Row=@Row+'<td>'+REPLACE(REPLACE(COALESCE([x].value('text()[1]','varchar(100)'),'NULL'),'<','&lt;'),'>','&gt;')+'<\/td>'-- +@CrLf\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @x.nodes('root\/row[3]\/*')\u00a0 AS a(x)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @@Rowcount>0\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @html=@HTML+@CrLf+'<tr>'+@Row+'<\/tr>',@row=''\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\t\u00a0\u00a0\u00a0 SELECT @html=@HTML+'<\/tbody><\/table>'\r\n\t\u00a0\u00a0\u00a0 END\r\n\t\/* 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 *\/\u00a0\u00a0\u00a0 \r\n\tSELECT @HTML='<!DOCTYPE html>\r\n\t<html>\r\n\t<head>\r\n\t<title>'+@@Servername+'-' +DB_NAME()+'<\/title>\r\n\t<\/head>\r\n\t<style>\r\n\t<!--\r\n\t\r\n\t.columnar { columns: 4; -moz-column-width: 15em; -webkit-column-width:15em; column-width: 15em; }\r\n\t\r\n\t.thetables { }\r\n\t\r\n\t\/* do the basic style for the entire table *\/\r\n\t.thetables table {\r\n\t\u00a0 \u00a0border-collapse: collapse;\r\n\t\u00a0\u00a0 border: none ;\r\n\t\u00a0\u00a0 font: 11px Verdana, Geneva, Arial, Helvetica, sans-serif;\r\n\t\u00a0\u00a0 color: black;\r\n\t\u00a0\u00a0 margin-left:20px;\r\n\t\u00a0\u00a0 margin-top: 20px;\r\n\t\r\n\t\u00a0\u00a0\u00a0 }\r\n\t\/*attach the styles to the caption of the table *\/\r\n\t.thetables table caption {\r\n\t\u00a0 font-weight: bold;\r\n\t\u00a0 text-align:left;\r\n\t\u00a0 padding-left:5px;\r\n\t\u00a0 background-color: #f3f3f3;\r\n\t}\r\n\t\u00a0\u00a0 \r\n\t\/*give every cell the same style of border *\/\r\n\t.thetables table td, .thetables table th, .thetables table caption { border: 1px solid #bbbde1\u00a0 ;\u00a0 vertical-align: top;\u00a0 }\r\n\t\/* apply styles to the odd headers *\/\r\n\t.thetables table th:nth-child(odd) { background-color: #cedfe2; }\r\n\t\/* apply styles to the even headers *\/\r\n\t.thetables table tr th:nth-child(even) { background-color: #dfebee; }\r\n\t\u00a0\/* apply styles to the even rows *\/\r\n\t.thetables table td {background-color: #f0f7f9;}\r\n\t\r\n\t.thetables table tr:nth-child(even) td:nth-child(odd){background-color: #f7fafb;\u00a0 }\r\n\t\u00a0\/* apply styles to the even colums of odd rows *\/\r\n\t.thetables table tr:nth-child(odd) td:nth-child(even){ background-color: #f7fafb; }\r\n\t\r\n\th1, ol { color: #000000; text-align: left; font: normal 11px Verdana, Geneva, Arial, Helvetica, sans-serif; }\r\n\t\r\n\th1 { font-size: 16px; font-weight: bold; color: #000000; text-align: left; }\r\n\t-->\r\n\t<\/style>\r\n\t\r\n\t<body>\r\n\t<h1>Sample of contents of tables in '+@@Servername+'-'+DB_NAME()+'<\/h1>\r\n\t<div class=\"columnar\"><ol>'+@Contents+'<\/ol><\/div>\r\n\t<div class=\"thetables\">\u00a0\u00a0\u00a0 \r\n\t'\u00a0\u00a0\u00a0 +Coalesce(@HTML,'')+coalesce(@errors,'')+'\r\n\t<\/div>\r\n\t<\/body>\r\n\t<\/html>'\r\n\t\/* and now we write out the result *\/\r\n\t\r\n\t--SELECT @FileNameAndPath=@Directory+REPLACE(REPLACE(REPLACE(@@Servername+'-'+DB_NAME(),'\/',''),'\\',''),':','')+'.html'\r\n\t--EXECUTE philfactor.dbo.spSaveTextToFile @html,@FileNameAndPath\u00a0 \r\n\tSelect @html as TheHTML, @Directory+REPLACE(REPLACE(REPLACE(@@Servername+'-'+DB_NAME(),'\/',''),'\\',''),':','')+'.html' as filename\r\n\t\r\n\t\"@\r\n\t\r\n\t<#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.#>\r\n\tForeach-DatabaseInServers $servers -verbose -TheDatabaseFilter\u00a0 { param($x); if ($x.name -like '*'){$x}} -jobToDo {\r\n\t\u00a0\u00a0 param($database)\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $result=$database.ExecuteWithResults($SQL) #execute the SQL\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $result.Tables[0]\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } |\u00a0 select-object ('TheHTML','filename') | & {PROCESS{ $_.TheHTML\u00a0 >$_.filename }}\u00a0 \r\n<\/pre>\n
    Conclusions<\/h2>\n
    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.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
    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.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4168,4635,4150,4151,4252],"coauthors":[6813],"class_list":["post-1852","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-powershell","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1852","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1852"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1852\/revisions"}],"predecessor-version":[{"id":71941,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1852\/revisions\/71941"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1852"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1852"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1852"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1852"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}