Picture this if you could. You inherit a new database either through the change of a job, or finding a black box database server, or maybe due to the acquisition of a new application.
No matter the cause, you are now responsible for maintaining that database and you also really need to become acquainted with it. Some of the acquaintance that is needed is learning how things are related and figuring out what data goes where. Not just what data, but what type of data.
I know that one of the things I am frequently asked is to figure out what data type belongs with which column. Furthermore, does this same column exist in more than one table. It’s nice when things are named consistently and you are able to tie things together by column name.
There are also times, when that column that is so consistently named, might need to be confirmed on data types between each of the implementations. I know I have run into cases when the column might be differently defined in different tables.
So, what to do to tie this all together and get that intimate acquaintance with your database?
Luckily, I have a script that will help you figure out some of these connections.
First, the script.
USE AdventureWorks2014; GO DECLARE @tablename sysname = NULL --'Store' /* NULL for all tables and table name for a specific table */, @isMSShipped TINYINT = NULL /* null for all, 0 for user objects, 1 for ms objects */, @ColNamesysname = NULL --'BusinessEntityID' /* null for all, column name to search all tables for specific column if tablename is null */; SELECT SCHEMA_NAME(t.schema_id) AS TabSchema ,t.name AS TableName , c.name AS ColumnName , c.column_id AS ColOrdPosit , dt.name AS DataType , c.max_length AS ColLength , c.precision AS Precision , c.scale AS Scale , dc.type_desc ,dc.definition FROM sys.tables t INNER JOIN sys.columns c ON c.object_id = t.object_id INNER JOIN sys.types dt ON c.user_type_id = dt.user_type_id --For system data types, user_type_id = system_type_id LEFT OUTER JOIN sys.default_constraints dc ON c.object_id = dc.parent_object_id AND c.column_id = dc.parent_column_id WHERE t.is_ms_shipped = ISNULL(@isMSShipped,t.is_ms_shipped) AND t.name = ISNULL(@tablename,t.name) AND c.name = ISNULL(@ColName, c.name) ORDER BY TabSchema, TableName, ColumnName;
Now, let’s give it a trial run. See if you can spot the difference by running that against the AdventureWorks database using the value “AccountNumber” for the @ColName parameter.
Maybe there is a legitimate reason for that Column to have a different data type specification than the other two. Maybe it doesn’t. One thing for certain is that you will be able to walk away with some questions to ask and hopefully gain a better understanding!
