Utility Queries–Column Metadata

Very often, I find myself wanting to query system metadata for columns. Some of the metadata is fairly easy to deal with, like if you want to find column names, just simply querying sys.columns is very straightforward. But if you want to query data types, it gets stickier. There are type types listed, one that is the declared type, and another for the base data type, which if you are using CREATE TYPE to create alias data types. So I started working on the query based on INFORMATION SCHEMA values, because it is a lot easier to work with. In my design book, I used that version (and will link this version in erratta/book updates.

But in the Books Online entry for the COLUMNS object (and others too), I discovered the following warning, a bit too late the change: “Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.” So I rewrote it to use the catalog views.

The query is fairly complex looking, but overall is pretty simple. I put comments through the code that explains what it does. I will note that I have tested the database using a case sensitive collation, but I haven’t tried it with a and CLR types. The query returns the following:

table_type – The type from sys.tables. Could be a user table, system table, user view, internal table

schema_name – The name of the schema of the table that the column is in

table_name – The name of the table

column_name – The name of the column

nullability – Whether that column is nullable, plus if the column is an identity column

declared_datatype – The data type as declared. For alias types (like sysname), this will be the name the user specified.

base_datatype – The data type as is implemented. For alias types this will be the base intrinsic type (like for sysname this will be nvarchar(128))

primary_key_column – A bit value, indicating that the column participates in a primary key constraint

column_id – The internal key of the column, which is used for ordering the columns

default_value – If there is a default constraint on the column, this will be the text of the declaration

column_check_constraint – If there is a check constraint that only deals with the one column, it will be considered a column check constraint. This will contain the text of the check constraint

table_check_constraint_reference – A bit value that indicates that there is one or more table level check constraints that reference the column

The query text follows. It is pretty unwieldy, so instead of the derived table you might want to consider building it into a view or even a procedure. I keep a SSMS project with all of my metadata queries that I maintain over time to call in any place I need them, so I use use this one ad-hoc. You can download the query from my website’s downloadable packages page here: http://www.drsql.org/Documents/ColumnMetadataQuery.sql

SELECT * FROM ( SELECT REPLACE(LOWER(objects.type_desc), '_', ' ') AS table_type, schemas.name AS schema_name, objects.name AS table_name, columns.name AS column_name, CASE WHEN columns.is_identity = 1 THEN 'IDENTITY NOT NULL' WHEN columns.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END AS nullability, --types that have a ascii character or binary length CASE WHEN columns.is_computed = 1 THEN 'Computed' WHEN types.name IN ( 'varchar', 'char', 'varbinary' ) THEN types.name + CASE WHEN columns.max_length = -1 THEN '(max)' ELSE '(' + CAST(columns.max_length AS VARCHAR(4)) + ')' END --types that have an unicode character type that requires length to be halved WHEN types.name IN ( 'nvarchar', 'nchar' ) THEN types.name + CASE WHEN columns.max_length = -1 THEN '(max)' ELSE '(' + CAST(columns.max_length / 2 AS VARCHAR(4)) + ')' END --types with a datetime precision WHEN types.name IN ( 'time', 'datetime2', 'datetimeoffset' ) THEN types.name + '(' + CAST(columns.scale AS VARCHAR(4)) + ')'

--timestamp should be reported as rowversion WHEN types.name = 'timestamp' THEN 'rowversion' --and the rest. Note, float is declared with a bit length, but is --represented as either float or real in types ELSE types.name END AS declared_datatype,

--types that have a ascii character or binary length CASE WHEN baseType.name IN ( 'varchar', 'char', 'varbinary' ) THEN baseType.name + CASE WHEN columns.max_length = -1 THEN '(max)' ELSE '(' + CAST(columns.max_length AS VARCHAR(4)) + ')' END

--types that have an unicode character type that requires length to be halved WHEN baseType.name IN ( 'nvarchar', 'nchar' ) THEN baseType.name + CASE WHEN columns.max_length = -1 THEN '(max)' ELSE '(' + CAST(columns.max_length / 2 AS VARCHAR(4)) + ')' END