I'm often reviewing existing databases and one of the first things I go looking for is the way they've used data types. In particular, I'm keen to know if they've used any deprecated data types (ie: ones that will/might be removed at some point). In our free SDU Tools for developers and DBAs, we added a procedure ListUseOfDeprecatedDataTypes to do just that, and to provide their details in a form that's easy to consume programmatically if you need that.
You can see how to execute it in the main image above. The procedure takes these 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'
One row is returned for each use of each deprecated data type.
The columns returned are SchemaName, TableName, ColumnName, DataType, and SuggestedReplacementType.
You can see it in action here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here:
2 thoughts on “SDU Tools: List use of Deprecated Data Types in a SQL Server Database”
I subscribed and got the download email but the service is unavailable with a 503 error
Hopefully just a transient error. I'll email you.