Background

I was schooled to make use of the humble readme.txt file to leave a record of any significant changes made to files or describe the reason for something new. The top line in the readme.txt being the most recent change with date, user name and a meaningful description. There were no fancy graphics, templates and all characters aligned perfectly as there was no complication from kerning. I used very primitive ascii-art on a few occasions otherwise a short sentence alone would be enough. We often used a graphical text editor called jot, otherwise we used vi.

As I moved onto PCs, I changed my note taking and documenting approach from simple text files to WordPerfect. These types of solutions are WYSIWYG for the purpose of printing so the text wraps onto new lines ensuring it prints to the page width. Other rich formatting features such as numbered lists, tables, columns, and diagrams make for many options for documentation patterns. These capabilities are certainly fundamental for effective documentation however an overkill for the humble change log. I began wasting time for ever heavier applications to even open.

As a result, a few years on and I'm going back to text files for basic readme.txt notes. During the past week, I found myself doing lots of manual aligning of text in a tabular arrangement for documenting in a script file. This gave me the thought that it would be handy to be able to generate this quickly. So this past Saturday I wrote a stored procedure to make a compact text representation of the results from a SELECT statement. Now that it is working, I find that I am mostly using this stored procedure when documenting basic reference table value changes from key tables, it is also useful to document new database objects, as well as adhoc tasks like drawing Sudoku games.

Example

I will work through the case of listing new tables added during the year. A Comment field is included for the purpose of adding a user comment against the table or view created during the year. The SQL to list table or views created within the year uses the where statement year(create_date) = year(getdate()). A constant of 30 dots is used for a Comment column in the example. The comment field can be edited with the keyboard set to insert mode so the dots will be overwritten and the boarder will not be displaced.

select name,
       type_desc,
       convert(varchar(10), create_date, 112) create_date,
       replicate('.', 30) Comment
from   sys.objects
where  year(create_date) = year(getdate())
       and type in ('U','V')

This SQL needs to be used as a parameter for the stored procedure sp_sqltable_to_text_format to produce the output. As there are single quotes in the statement these need to be duplicated so they are not interpreted during execution.

exec dbo.sp_sqltable_to_text_format @SQL = 'select name,
       type_desc,
       convert(varchar(10), create_date, 112) create_date,
       replicate(''.'', 30) Comment
from   sys.objects
where  year(create_date) = year(getdate())
       and type in (''U'',''V'')'

After the code is execute in Query Analyser it can be pasted into a text document and edited in a text editor. In this example the intention is to edit the comment inplace using the keyboard in insert mode so the dots are overwritten without displacing the table boundary. In the example I have filled in comments for the first two table using the mentioned method.

+----------------------+-------------+--------------+---------------------------------+
| name                 | type_desc   | create_date  | Comment                         |
+----------------------+-------------+--------------+---------------------------------+
| tMatchWikiNgramStem  | USER_TABLE  | 20171001     | Temporary drop when user done.  |
| tTalk                | USER_TABLE  | 20170710     | Base tracking table...........  |
| lSound               | USER_TABLE  | 20170710     | ..............................  |
| lPronounce           | USER_TABLE  | 20171123     | ..............................  |
| vtmp                 | VIEW        | 20171210     | ..............................  |
| tNgram1              | USER_TABLE  | 20170926     | ..............................  |
+----------------------+-------------+--------------+---------------------------------+

Code walk through

The stored procedure only requires a valid SQL select statement to operate with syntax as outlined below.

exec dbo.sp_sqltable_to_text_format @SQL = '<select statement>'

The first step within the procedure is to execute the parsed SQL using a SELECT INTO statement to capture the results of the query into a working table temptxt1.

set @SQL = 'select * into temptxt1 from ('+@SQL+') x'
exec (@SQL)

The next step of the procedure is to replace the non-printing characters tab, line feeds and carriage return with a single space in the working table.

select @SQL = @SQL + ' update temptxt1 set [' + COLUMN_NAME + '] = replace([' + COLUMN_NAME + '],char(9),'' '') where [' + COLUMN_NAME + '] like ''%''+char(9)+''%'''
              + ' update temptxt1 set [' + COLUMN_NAME + '] = replace([' + COLUMN_NAME + '],char(10),'' '') where [' + COLUMN_NAME + '] like ''%''+char(10)+''%'''
              + ' update temptxt1 set [' + COLUMN_NAME + '] = replace([' + COLUMN_NAME + '],char(13),'' '') where [' + COLUMN_NAME + '] like ''%''+char(13)+''%'''
from   INFORMATION_SCHEMA.COLUMNS
where  TABLE_NAME = 'temptxt1'  and DATA_TYPE in ('nvarchar','nvarchar','char')
exec (@SQL)

Using the INFORMATION_SCHEMA.COLUMNS table, the column names of temptxt1 are used to prepare a statement to take the max len of each field. IsNull is used to return a Value of 2 if max len returns nulll. Since the column name still needs to be accommodated, if the length of the column name is longer than the max value length, the length of the COLUMN_NAME is used for the column width.

select @SQL = Stuff((select ' ' + SEL
                     from   (select 'union all select  ' + Cast(ORDINAL_POSITION as varchar(10)) + ' POS, isnull(max(len([' + COLUMN_NAME + '])),2)+1 WIDTH, ''' + COLUMN_NAME + ''' COL from temptxt1' SEL
                             from   INFORMATION_SCHEMA.COLUMNS
                             where  TABLE_NAME = 'temptxt1') x
                     FOR XML PATH ('')), 1, 11, '')
set @SQL='select POS, COL, case when len(COL) > WIDTH then len(COL)+1 else WIDTH end WIDTH into temptxt2 from ('+@SQL+')x'
exec (@SQL)

The next step is to construct the three parts for the output: the horizontal line, field labels, and body values of the table.

+--------+-------+
| Field  | field |
+--------+-------+
| value  | value |
| value  | value |
+--------+-------+

The variable @top is for the horizontal lines that are constructed using replicate with a ‘–‘ symbol. A ‘+’ symbol is used on either end of the fields.

select @top = '+'+stuff((select '-'+replicate('-',WIDTH)+'+' from
(select top 100 percent * from temptxt2) x
order by POS asc
FOR XML PATH ('')), 1, 0, '')

The variable @columnnames is assigned a concatenation of a ‘|’ symbol, column name and padded with spaces to the length of width.

select @columnnames = '|'+stuff((select ' '+COL+replicate(' ',WIDTH-len(COL))+'|' from
(select * from temptxt2) x
order by POS asc
FOR XML PATH ('')), 1, 0, '')

The body record part is constructed by casting each field to char of the width determined from the max len process from the temptxt2 table. For simplicity of the output this is executed and using SELECT INTO stored in temptxt3.

select @SQL = '''|'''+stuff((select '+'' ''+cast(isnull(['+COL+'],'''') as char('+cast(WIDTH as varchar(10))+')) collate database_default+''|''' from
(select * from temptxt2) x
order by POS asc
FOR XML PATH ('')), 1, 0, '')
set @SQL = 'insert into temptxt3 (Body) select * from (select '+@SQL+' Body from temptxt1) Text'
exec(@SQL)

At this point the three parts for the output are complete. The final step is to UNION ALL parts together so the output is complete.

select @top Output union all
select @columnnames union all
select @top x union all
select Body from temptxt3 union all
select @top

Conclusion

The stored procedure is a useful tool that supports various frequently performed tasks. Most significantly it streamlines the way I can continue documenting using simple text files. The procedure has been attached here.