SDU Tools: List use of Deprecated Data Types in a SQL Server Database -> Updated

I’ve previously posted about the procedure ListUseOfDeprecatedDataTypes in our free SDU Tools for developers and DBAs. I mentioned that I’m often reviewing existing databases and one of the first things I go looking for is the way they’ve used data types, and that in particular, I’m keen to know if they’ve used any deprecated data types (i.e. ones that will/might be removed at some point).

The procedure was updated in version 21 to now include a ChangeScript column. (Thanks to Michael Miller for the suggestion).

It still has the same parameters:

@DatabaseName sysname - This is the database to process @SchemasToList nvarchar(max) - a comma-delimited list of schemas to include (ie: ‘Sales,Purchasing’) or the word ‘ALL’ @TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: ‘Customers,Orders’) or the word ‘ALL’ @ColumnsToList nvarchar(max) - a comma-delimited list of columns to include (ie: ‘CustomerName,StreetAddress’) or the word ‘ALL’

And still returns one row for each use of each deprecated data type.

The columns returned are SchemaName, TableName, ColumnName, DataType, SuggestedReplacementType, and now also ChangeScript.

You can see the previous version in action here:

In our own internal version, we also flag money and smallmoney types as we don’t want them used, but the tool doesn’t flag those.

But let’s look at an example of using the update, in this case against msdb. (I wish msdb wasn’t still using types that were deprecated in 2005 and it’s now 2022):

EXEC SDU_Tools.ListUseOfDeprecatedDataTypes 
    @DatabaseName = N'msdb',
    @SchemasToList = N'ALL', 
    @TablesToList = N'ALL', 
    @ColumnsToList = N'ALL';

Deprecated types in msdb