We all know that SQL Server database development is fun! Still, there are some tasks involved that seem harder than they should be. You can make those tasks easier with SQLFacts, a FREE suite of tools for SQL Server database engineers. Please refer to an earlier article for a general description of the SQLFacts toolkit.

SQLFacts provides many tools for SQL Server professionals:

• A previous article covered the index-related performance tuning tools in SQLFacts.
• A previous article covered the query-related performance tuning tools in SQLFacts.
• A previous article covered the monitoring and status tools in SQLFacts.

This article will focus on the database development and documentation tools in SQLFacts. Be sure to see the README file in the SQLFacts toolkit download for more information about all the tools.

SQLFacts

NOTE: SQLFacts is the name of the suite, but it's also the name of a powerful tool in the suite.

The SQLFacts tool is for conducting research on a single SQL Server database. It returns 48 result sets by itself. Some of the result sets are detailed lists of what exists and some contain a lot of analysis. There are several result sets to identify many "SQL Code Smells" and database architecture smells. The Messages tab in SQL Server Management Studio (SSMS) will contain a key/legend for the result sets (see below). The SQLFacts result sets (facts) provide a wealth of information.

• Fact 01 Filegroups
• Fact 02 Database Files
• Fact 03 Database Users
• Fact 04 Schemas
• Fact 05 Schema Permissions
• Fact 06 Table Details, by name
• Fact 07 Table Details, by row count
• Fact 08 Partitions (Table)
• Fact 09 Partitions (Index)
• Fact 10 Primary Keys
• Fact 11 Alternate Keys
• Fact 12 Indexes
• Fact 13 Foreign Keys
• Fact 14 Check Constraints
• Fact 15 Triggers
• Fact 16 Table Columns
• Fact 17 Routine Columns
• Fact 18 Routine Parameters
• Fact 19 Table/Routine Permissions
• Fact 20 Sequences
• Fact 21 Synonyms
• Fact 22 External References
• Fact 23 Internal References, by object caller
• Fact 24 Internal References, by object called
• Fact 25 Table Summary, by name
• Fact 26 Table Summary, by layer
• Fact 27 Table Summary, by estimate
• Fact 28 Routine Summary, by name
• Fact 29 Routine Summary, by layer
• Fact 30 Routine Summary, by estimate
• Fact 31 Data Type Analysis (Primary Keys)
• Fact 32 Data Type Analysis (Table Columns)
• Fact 33 Data Type Analysis (Routine Parameters)
• Fact 34 Name Analysis (Table Columns)
• Fact 35 Name Analysis (Routine Parameters)
• Fact 36 Foreign Keys To Consider
• Fact 37 Foreign Key Indexes
• Fact 38 Index Redundancy
• Fact 39 Questionable Indexes
• Fact 40 Questionable Tables
• Fact 41 Questionable Foreign Keys
• Fact 42 Questionable Constraints
• Fact 43 Questionable Defaults
• Fact 44 Questionable Routines
• Fact 45 Questionable Data Types
• Fact 46 Questionable Names
• Fact 47 Questionable Parameters
• Fact 48 Questionable References

The SQLFacts tool includes a tiny utility application to export all the result sets to HTML pages (and CSV files), with a table of contents page, to serve as documentation for the database architecture. You can view an example for the AdventureWorks2019 database. The result sets can also be saved as tables by doing a simple global SQL code change.

There's a separate text file (SQLFacts.txt) in the toolkit download containing general information and descriptions of all the SQLFacts result sets (facts).

Browse

The Browse tool is for conducting research while doing database development or performance tuning. The schema and name of any table or routine (view, stored procedure, function, or trigger) is entered near the start of the SQL code.

If the specified object is a table then the Results tab will contain 12 SQLFacts result sets (facts) for the table. The columns in the first result set define the order and content of the subsequent result sets. The Messages tab will contain a key/legend for the result sets. The Messages tab will also contain some generated SQL statements to be used as templates for other SQL statements.

If the specified object is a routine (view, stored procedure, function, or trigger) then the Results tab will contain five SQLFacts result sets (facts) for the routine. The Messages tab will contain a key/legend for the result sets. The Messages tab will also contain the SQL code definition of the routine. The Messages tab will also contain some generated SQL statements to be used in an emergency if/when the routine suddenly runs much longer than expected.

The result sets provide very useful detail and context for the object in question.

References

The References tool is for conducting research while doing database development. The schema and name of any table or routine (view, stored procedure, function, or trigger) is entered near the start of the SQL code.

If the specified object is a table then the Results tab will contain two result sets. The first result set contains a row for every ancestor (parent, grandparent, and more) of the table. The second result set contains a row for every descendant (child, grandchild, and more) of the table.

If the specified object is a routine (view, stored procedure, function, or trigger) then the Results tab will contain two result sets. The first result set contains a row for every ancestor (called, called by called, and more) of the routine. The second result set contains a row for every descendant (caller, caller of caller, and more) of the routine.

The result sets provide a way to assess the upstream/downstream effects of changes.

Search

The Search tool is for conducting research while doing database development. A search string is entered near the start of the SQL code.

There are three variants of the Search tool:

• Search_Fast does not support any wildcard searches, but it's very fast.
• Search_Wild supports LIKE-style searches, but it's more time-consuming.
• Search_Name searches names of objects/columns.

The Search_Fast (or Search_Wild) variant finds the search string within the SQL code definition of any routine (view, stored procedure, function, or trigger) in the current database. The routine definitions are parsed into lines and each line is compared to the search string.

The Search_Fast (or Search_Wild) variant also finds the search string within SQL Server Agent job steps. The subsystem for a matching step must be "TSQL" and the database must be the current database.

The Search_Fast (or Search_Wild) variant can optionally ignore comments within the SQL code definition of any routine or job step. This is a very rare (maybe even unique) feature that can often eliminate a lot of false matches.

The Search_Fast (or Search_Wild) variant returns two result sets:

• Result set 1 is a summary of the search results. It contains the number of matching lines for each involved routine.
• Result set 2 is the actual details of the search results. It contains every matching line of every involved routine.

The Search_Name variant finds the search string within the names of objects/columns in the current database. It returns one result set with matching items.

GenerateKeys

The GenerateKeys tool is for generating DDL SQL statements to implement primary key constraints, foreign key constraints, and foreign key indexes.

The tool assumes that you are using a common (although far from universal) database architecture:

• The tool assumes the tables are using single-column surrogate primary keys.
• The tool assumes the primary key column names are derived via the table names.
• The tool assumes the primary keys migrate to child tables to become foreign keys.

The generated foreign key indexes are merely a starting point for a comprehensive indexing strategy. They are not intended to be a final set of indexes.

GenerateSQL

The GenerateSQL SQL tool is for returning information for potential JOIN operations. It also generates a set of SELECT statements with JOIN clauses.

There are four variants of the GenerateSQL tool:

• GenerateSQL_All generates SQL statements for all tables, views, and table-valued functions.
• GenerateSQL_2 generates SQL statements for a pair of objects with any connecting objects.
• GenerateSQL_IN generates INSERT statements for all tables.
• GenerateSQL_UP generates UPDATE statements for all tables.

GenerateSQL_All:

The Messages tab will contain generated SELECT statements, one for each table, view, or table-valued function. The SELECT statements include JOIN clauses for parents and children. The SELECT statements are not intended to be used verbatim. Instead, copy column list elements and JOIN clauses from one SELECT statement, or several SELECT statements, to create a custom query as needed.

There's a variable near the start of the SQL code to determine if brackets are used on object/column names in the generated SQL code.

• Result set 1 contains all unique indexes, whether they are primary keys, unique constraints, or standard unique indexes. They are the primary (parent) objects for JOIN operations. The involved columns are included.
• Result set 2 contains all discernable ways to JOIN primary (parent) objects to foreign (child) objects. The foreign (child) objects can be tables, views, or table-valued functions. The potential JOIN operations come from foreign keys, or by matching column names and column data types with unique index keys. The involved columns are included.

GenerateSQL_2:

The Messages tab will contain generated SELECT statements, one for each row in the result set. The SELECT statement column list includes everything from each object in the query path. The SELECT statement FROM clause is for object 1, followed by a JOIN clause for each connecting object, followed by a JOIN clause for object 2. The SELECT statements can be used verbatiom, or modified as needed.

There's a variable near the start of the SQL code to determine if brackets are used on object/column names in the generated SQL code.

There are variables near the start of the SQL code to specify the schema names and object names for a pair of objects. The objects can be tables, views, or table-valued functions.

The result set contains a list of possible query paths between the pair of objects. The list includes only the shortest paths, involving the fewest JOIN operations.

GenerateSQL_IN:

The Messages tab will contain generated INSERT statements, one for each table. The INSERT statements are not intended to be used verbatim. They are intended as templates to avoid writing mundane code.

There's a variable near the start of the SQL code to determine if brackets are used on object/column names in the generated SQL code.

GenerateSQL_UP:

The Messages tab will contain generated UPDATE statements, one for each table. The UPDATE statements are not intended to be used verbatim. They are intended as templates to avoid writing mundane code.

There's a variable near the start of the SQL code to determine if brackets are used on object/column names in the generated SQL code.

Summary

SQL Server database development is rewarding work, but it has a few challenges. The challenges can be overcome with the proper tools. SQLFacts is a free toolkit with many tools for SQL Server developers. Download it now, try the many features, and enjoy the work even more!