Currently, all headers are always shown. If a database does not have any Views, it should not have a Views header included in the output.

• This requires checking for object type existence before building ToC and headers
• At least one unit test needs updating to account for the change in the minimum number of rows being output (as a proxy smoketest for results being returned correctly)

Support Azure SQL + automate tests against it

To graph out query results

Most likely added by accident by not specifying a size on creation.

https://tsqlt.org/

Either detect or use supplied parameters to check common migration tables created by tools like DbUp and Flyway to be able to add a "latest version" to the database documentation.

When code fencing parameter is added: DavidAnson/markdownlint#292

in sp_helpme

Research writing dynamic Pester wrappers for tsqlt that will allow the tests to be recognized in AppVeyor with possibly more standardized output that is easier to work with.

Is your feature request related to a problem? Please describe.
Add indexes

Reduce size, optimize!

as per the doc file's feature list

As they can cause unnecessary extra storage usage and are usually a bad idea.

https://www.red-gate.com/simple-talk/blogs/reasons-why-you-may-not-want-to-use-a-heap/

Adding code coverage for the Azure SQL tests should make the total close to 100%, but first need this resolved:

GoEddie/SQLCover#51

sp_sizeoptimiser was originally made to be compatible with much older versions of SQL Server. However, now the suite is locked to 2012+ so there are improvements that can be made (as well as general refactoring).

Describe the bug
For an unknown reason, when running the (now commented out) unit test for sp_doc regarding data sensitivity classifications, the query always times out (up to 10 minutes) in appveyor.

To Reproduce
I can't reproduce it locally OR when RDP'd into the Appveyor machine, but will try the latter again in a dedicated branch. Might have to do with a recent CU applied to 2019?

Expected behavior
The unit test runs in a reasonable amount of time (few seconds).

Versions(please complete the following information):

• OS: Windows
• SQL Server: 2019
• SSMS: N/A

Additional context
It used to work and nothing in that particular test has changed recently.

Check column names for something like '%TYPE' that are actually containing strings. These may be better off being FKs to lookup tables for normalization / size reduction purposes.

Limit to text fields probably, ignore numeric ones.

RAISERROR(@string, 10, 1) WITH NOWAIT

I executed the procedure with the parameters below. I found that check_num values 7 and 9 output the db_name and obj_type reversed in the final results.
...
6 Data Types [MyDatabase] USER_TABLE [dbo].[Table08] [Column01] Best practice is to use DECIMAL/NUMERIC instead of float for non floating point math. http://lowlydba.com/ExpressSQL/#float-and-real-data-types
7 Data Types USER_TABLE [MyDatabase] [dbo].[Table09] [Column02] Deprecated data type in use: image. http://lowlydba.com/ExpressSQL/#deprecated-data-types
9 Data Types USER_TABLE [MyDatabase] [dbo].[Table10] [Column03] Column is NUMERIC(10,0) . Consider using an INT variety for space reduction since the scale is 0. http://lowlydba.com/ExpressSQL/#numeric-or-decimal-0-scale)
...

DECLARE @include SizeOptimiserTableType;
DECLARE @exclude SizeOptimiserTableType;

INSERT INTO @include ([database_name])
VALUES (N'MyDatabase');

EXEC [dbo].[sp_sizeoptimiser]
@IndexNumThreshold = 1,
@IncludeDatabases = @include,
@ExcludeDatabases = @exclude,
@IncludeSysDatabases = 0,
@IncludeSSRSDatabases = 0,
@IsExpress = 0;

Describe the bug
I ran this code on my table and received the below error

EXEC [sp_estindex] @SchemaName = 'dbo'
				  ,@TableName = 'MyTable'
				  ,@IndexColumns = N'MyField'
				  ,@Verbose = 1

Msg 50000, Level 16, State 2, Procedure sp_estindex, Line 760 [Batch Start Line 0]
[sp_estindex]: Arithmetic overflow error converting expression to data type int.

Issue is on this line. Since my NumLeafPages: 829971, it overflows @LeafSpaceUsed after multiplying by 8129
SET @LeafSpaceUsed = 8192 * @NumLeafPages;

SQL server 2016

Thanks!

Is your feature request related to a problem? Please describe.

Currently, my descriptions in ms_descriptions are in Markdown, because I tried to use SchemaSpy to document databases. And I tried SchemaSpy it because it supports markdown in descriptions.

Now I tried to document these databases with sp_doc, but the result in unusable when I include to document the ms_description: The markdown from the descriptions interfere with the generated markdown.

I don't know if it would be possible to "nest" markdown. But I am afraid, this is not possible. But maybe there could be an option to keep the content of the markdown descriptions as they are, but to mark them as code?

Of course there are also code blocks inside the markdown descriptions, and it looks like this will not work, because markdown has no clear syntax to mark the end of some code block:

## markdown description

bla bla bla

select a
from b

and some line after the code

I could use a language marker for the inner code, but again, markdown interpreter will not find detect this as nested code.

## markdown description

bla bla bla

```sql
select a
from b

and some line after the code

Describe the solution you'd like

• An alternative would be an option to generate asciidoc files as an alternative to markdown.
  The Syntax would allow nested code, GitHub and some other sites support not only markdown, but also asciidoc.
• An option to include the description as code could help.
  If not using code blocks in the description then the outer usage of code block could work.
  • Maybe the code marker could be "escaped" in any useful way. It looks note fine, but it would not interfere with the main part of the documentation:

## markdown description

bla bla bla

'```
select a
from b
'``` 

and some line after the code

Describe alternatives you've considered

To exclude the descriptions from documentation. But it makes no sense to have a full documentation in markdown in the descriptions and not to use it.

_'s in links don't work but may be prevalent in object naming conventions (see wideworldimporters)

Describe the bug
Reportedly a large markdown (~3MB, mostly due to large stored procs like sp_blitz, whoisactive, etc.) causes memory issues in some IDEs (Data Grip, for one) when loading the markdown file.

Fix
Introduce a parameter to control a default max length for large stored procedures (8k?) to prevent ballooning of the markdown's file size.

Fix reference URLs now that the domain for this project has changed

For sp_doc & see if tsqlt can handle the version check as a test somehow

It got screwed up during some cleanup efforts, needs to be revisited on the dev branch and cleaned up / standardized.

When I execute the sp_sizeoptimiser stored procedure against my database I get the following error message:

...
Check 15 - Inefficient indexes
Msg 50000, Level 16, State 30, Procedure dbo.sp_sizeoptimiser, Line 1209 [Batch Start Line 1245]
String or binary data would be truncated.

Debugging the code I found that the [obj_name] columns were using sysname. I increased the size of the [obj_name] columns to NVARCHAR(1024) and it fixed the problem.

Describe the bug
When there is no object in a section (views for example), the section is not present thanks to the first check:
IF EXISTS (SELECT 1 FROM [sys].[views] WHERE [is_ms_shipped] = 0)
However, the closing is there beause there is no check

To Reproduce
Run sp_doc on a database with no views, or no procedures

Expected behavior
Either the 'Begin ... End' statement should be for the whole section, or there should be another check for the 'End collapsible view section' part

Versions(please complete the following information):

• OS: Windows
• SQL Server: 2019
• SSMS: 18.6

Additional context
If you tell me if you prefer Being ... End (my favored solution) or add another IF at the end of the section, I can do a PR.

Describe the bug
Check 16 - Architecture adds extra brackets around the database name, i.e. [[database]]

Probably want to move them to a dedicated folder, then cmd loop through them instead of hard coding each.

Maybe break out tests separately too?

Is your feature request related to a problem? Please describe.
Add user defined data types

To test all checks in each version of SQL that appveyor lets us work with.

During the appveyor build process, a new WideWorldImporters database documentation file should be produced in /docs to allow for easier spotting of visual bugs before the merging new code into master (such as #122). This will also allow for markdown linting of the output file.

The backup can be fetched from https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0 to restore on the fly.

Since tSQLt is being actively released again, it makes more sense to start downloading the latest to run tests with.

This is currently blocked by tSQLt-org/tSQLt#70 which prevents the new version from working with Azure SQL.

I get an error when trying to execute the [sp_OptiMiser] stored procedure on a SQL Server 2017 (Standard Edition) machine. From the Messages window:

sp_OptiMiser

Time: Sep 27 2018 12:10PM
Express Edition: 0
SQL Major Version: 14
SQL Minor Version: 3037
Sparse Columns Feature: 1

Building results table...
Running size checks...

Check 1 - Date and Time Data Types
Check 2 - Archaic varchar Lengths
Check 3 - Unspecified VARCHAR Length
Check 4 - Mad MAX VARCHAR
Check 5 - Use of NVARCHAR (EXPRESS).
Skipping check, not express...
Check 6 - Use of FLOAT/REAL data types
Check 7 - Deprecated data types
Check 8 - BIGINT used for identity columns (EXPRESS)
Skipping check, not Express...
Check 9 - NUMERIC or DECIMAL with scale of 0
Check 10 - Data file growth set past 10GB (EXPRESS).
Skipping check, not express...
Check 11 - Data file growth set to percentage.
Check 12 - Non-default fill factor (EXPRESS)
Skipping check, not express...
Check 13 - Questionable number of indexes
Check 14 - Sparse column eligibility
Msg 213, Level 16, State 7, Line 6
Column name or number of supplied values does not match table definition.

Is your feature request related to a problem? Please describe.
Add some database-level data at the top of sp_doc. Might include server name, compatibility mode, creation date, etc.

This will bring better feature parity with other tools like Redgate's SQL doc and Apex SQL Doc

An optional mode to generate documentation utilizing emojis to make it more fun and potentially easier to skim.

Ideas:

• Yes/No -> ✔️/ ❌
• PK -> 🔑
• FK -> 🗝️

Describe the bug

there is a missing line between the <details> block and the list of synonyms

To Reproduce

• document any database containing synonyms
• look at the generated markdown

I used powershell

$Query = "EXEC sp_doc @DatabaseName = 'dhw_self';"
Invoke-DbaQuery -SqlInstance localhost\sql2019 -Database master -Query $Query -As SingleValue | Out-File dhw_self\readme.md

Expected behavior

There should be an empty line in front of the list of synonyms. It is correct implemented for other objects

## Synonyms

<details><summary>Click to expand</summary>

* [sys_dwh.columns](#sys_dwhcolumns)
* [sys_dwh.computed_columns](#sys_dwhcomputed_columns)
* [sys_dwh.default_constraints](#sys_dwhdefault_constraints)
* [sys_dwh.dm_exec_describe_first_result_set](#sys_dwhdm_exec_describe_first_result_set)
* [sys_dwh.dm_sql_referenced_entities](#sys_dwhdm_sql_referenced_entities)
* [sys_dwh.extended_properties](#sys_dwhextended_properties)
* [sys_dwh.foreign_key_columns](#sys_dwhforeign_key_columns)
* [sys_dwh.foreign_keys](#sys_dwhforeign_keys)
* [sys_dwh.identity_columns](#sys_dwhidentity_columns)
* [sys_dwh.index_columns](#sys_dwhindex_columns)
* [sys_dwh.indexes](#sys_dwhindexes)
* [sys_dwh.objects](#sys_dwhobjects)
* [sys_dwh.parameters](#sys_dwhparameters)
* [sys_dwh.schemas](#sys_dwhschemas)
* [sys_dwh.sp_addextendedproperty](#sys_dwhsp_addextendedproperty)
* [sys_dwh.sp_updateextendedproperty](#sys_dwhsp_updateextendedproperty)
* [sys_dwh.sql_expression_dependencies](#sys_dwhsql_expression_dependencies)
* [sys_dwh.sql_modules](#sys_dwhsql_modules)
* [sys_dwh.tables](#sys_dwhtables)
* [sys_dwh.types](#sys_dwhtypes)

### sys_dwh.columns


| Synonym | Base Object |
| --- | --- |
| sys_dwh.columns | [dhw_self.sys.columns](#sys.columns) |

[Back to top](#dhw_self)

Versions(please complete the following information):

• OS: [e.g. iOS] Windows 10
• SQL Server: [e.g. chrome, safari] SQL Server 2019
• SSMS: should not be important, I used powershell

Additional context
Add any other context about the problem here.

Since this uses a table value parameter, it might be best to include a concrete usage example.

Identifying code blocks using tsql works on Github.com but does not translate well for github pages generated sites - switch code blocks to use sql as the code instead to increase readability overall.

Applying linting to standardize the code quality / formatting.

Probably will leverage https://github.com/tsqllint/tsqllint as that has the best T-SQL specific support I've seen.

Always wanted to see how much of space used by the table and number for rows in it with cmd

so plugging the output of sp_spaceused

For SQL 2019+ : https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sensitivity-classifications-transact-sql?view=sql-server-ver15

Primary key columns should be indicated in some way (probably bold text).

for all scripts

For SQL Server 2017+, look into dockerizing unit testing. This should be faster than relying on Appveyor's individual build images and allow lift and shift to another CI tool later on.

if it exists