SDU Tools: Listing all the Data Types Used in a SQL Server Database

 

When I first start to work with a database that I haven’t seen before, one of the things that I check immediately is the type of data being stored. The list of existing data types often gives me interesting insights into the design of the database, and how it interacts with application code.

The list of data types also shows me details of the consistency (or lack of) in the design.

In the image above, you can see the data types that I used when creating the WideWorldImporters database samples for Microsoft. This listing was created by our ListAllDataTypesInUse stored procedure in our free SDU Tools suite.

For another example of it’s use, let’s take a look at the AdventureWorks sample database, and see what it shows us:

The parameters to the procedure are the name of the database to check, and a list of schemas, tables, and/or columns to limit the search to. For each one, you can provide the word ALL, or a comma-delimited list of values. For example, @SchemasToList = ‘HumanResources,Sales’ would limit the output to tables in the HumanResources and Sales schemas.

In this case, you can already see a few things. First is that user-defined data types have been used (note AccountNumber in row 1, Flag in rows 9, Name in row 14, and NameStyle in row 15).

Decimal values have a precision of both 8 and 9, and scales of 2 and 4. That’s often interesting to see how decimal values are stored, and how rounding is being used in the application.

I’m not a fan of the money data type (it’s an old Sybase type), but note that it is used, as is numeric, along with the decimal values. That would have me  a bit curious.

Obviously, I’d be more concerned if I found text, ntext, or image data types, as they are deprecated.

I tend to also rarely use nchar, so I’d be a bit interested in what those values are for.

This part of the list let’s me get an idea how string values are stored. It also lets me start to find issues like email addresses or product descriptions that are stored in different size columns in different parts of the database.

And finally I get to notice some more user-defined types, plus smallmoney (likely a red flag for me), some GUID values, and some xml.

You can see it in action here:

For more information on our free SDU Tools, please visit:

http://sdutools.sqldownunder.com

2018-01-10