SQL Server Find Columns by Data Type Across the Database

calendar May 22, 2026 / · 7 min read · sql server columns data types schema analysis sql scripts sys.columns sys.types sys.tables sys.schemas database structure metadata sys.computed_columns sys.dm_db_index_physical_stats sys.foreign_keys sys.objects sys.views  ·
Share on: linkedin copy

"Where is every datetime column?" — "Which tables still use text instead of varchar(max)?" — "Are any columns using a deprecated user-defined type?" These are recurring DBA questions during schema audits, deprecation sweeps, and pre-migration assessments. This T-SQL script joins sys.columns to sys.types, sys.tables, and sys.schemas to produce the canonical answer: every column in the current database with its full type signature.

Purpose and Overview

Which columns survived the move from text to varchar(max)? Which fields still carry the deprecated Money_T UDT three releases after it was officially retired? Did a recent merge silently reintroduce datetime on a column the design review thought it had migrated to datetime2? A single catalog-view query against sys.columns and sys.types answers all three — and every other question of the form "where is every column of type X in this database?" — without an INFORMATION_SCHEMA round trip or a manual table-by-table sweep.

The query reads four catalog views together: sys.columns (one row per column on every table or view), sys.types (one row per system or user-defined data type), sys.tables (one row per user table), and sys.schemas (schema-name resolution). Each result row carries the schema name, table name, column name and ordinal, the data-type name and its home schema, three boolean flags (is_user_defined, is_assembly_type, is_nullable), and the precision/scale/max_length details that distinguish decimal(18,2) from decimal(38,8). Add a WHERE t.name = N'datetime' clause to scope the report to a single type — the canonical answer to "show me every datetime column."

Code Breakdown

The complete script is shown below. Run it in the database whose columns you want to audit.

 1SELECT s.name           AS [schema],
 2       ts.name          AS TableName,
 3       c.name           AS column_name,
 4       c.column_id,
 5       SCHEMA_NAME(t.schema_id) AS DatatypeSchema,
 6       t.name           AS Datatypename,
 7       t.is_user_defined,
 8       t.is_assembly_type,
 9       c.is_nullable,
10       c.max_length,
11       c.PRECISION,
12       c.scale
13FROM   sys.columns AS c
14INNER JOIN sys.types AS t  ON c.user_type_id = t.user_type_id
15INNER JOIN sys.tables ts   ON ts.OBJECT_ID    = c.OBJECT_ID
16INNER JOIN sys.schemas s   ON s.schema_id     = ts.schema_id
17ORDER BY s.name, ts.name, c.column_id;

sys.columns — One Row per Column

sys.columns is the per-column catalog view. Each row carries object_id (the parent table or view), column_id (the 1-based ordinal of the column), name, is_nullable, max_length, precision, scale, and two type-id columns: system_type_id and user_type_id. For built-in types the two values are equal; for user-defined types user_type_id carries the UDT identifier while system_type_id carries the underlying built-in type the UDT is based on. The script joins on user_type_id so user-defined types resolve to their own name (not to the underlying base type).

sys.types — Built-in and User-Defined Types

sys.types has one row per system type (int, varchar, nvarchar, datetime, etc.) and one row per user-defined or CLR data type. The boolean columns is_user_defined and is_assembly_type separate the three categories: built-in types have both flags set to 0; classic CREATE TYPE user-defined types have is_user_defined = 1 and is_assembly_type = 0; CLR types loaded from an assembly have both flags equal to 1. The schema_id column lets you resolve the type's home schema — system types live in sys, user-defined types live in whichever schema they were created in (typically dbo).

sys.tables — Filter to User Tables Only

Joining sys.columns to sys.tables (rather than sys.objects) restricts the result set to user tables. Views, table-valued function return types, and other object types that also have columns won't appear. To include views, swap sys.tables for sys.objects and filter on type IN ('U', 'V').

sys.schemas — Schema Name Resolution

sys.schemas provides the name column from a schema_id. Two schema-name columns are reported in the output: the schema of the table (s.name) and the schema of the data type (SCHEMA_NAME(t.schema_id) AS DatatypeSchema). These differ for user-defined types: a user-defined type created in dbo and used by a table in sales will show schema = 'sales' and DatatypeSchema = 'dbo'.

max_length, precision, and scale — Type Discriminators

For most types precision and scale are zero and only max_length matters. For varchar(50) max_length is 50; for nvarchar(50) it is 100 (two bytes per character); for varchar(MAX) and nvarchar(MAX) it is -1. For numeric types precision is the total digits and scale is the digits to the right of the decimal — decimal(18,2) returns precision 18, scale 2. The combination of type name + max_length + precision + scale is what fully identifies "the same type" across columns.

Key Benefits and Use Cases

  • Find every column matching a specific type: add WHERE t.name = N'datetime' (or any other type) to scope the report
  • Locate deprecated types: filter on t.name IN ('text', 'ntext', 'image') to find columns using the legacy LOB types that need migration to varchar(max), nvarchar(max), and varbinary(max)
  • Audit user-defined type usage with WHERE t.is_user_defined = 1 — particularly useful when retiring a UDT and needing the list of column-level changes required
  • Pre-migration assessment when moving a database to a different platform or version with different type support (e.g., migrating to Azure SQL DB where certain types behave differently)
  • Schema-name-aware output handles multi-schema databases such as Dynamics, NAV, SharePoint, and any database that uses schemas as a logical boundary
  • Identifies columns with very large declared sizes by sorting on max_length DESC to spot over-provisioned strings

Performance Considerations

  • Metadata-only: reads four catalog views with no user-data access. Runs instantly even on databases with hundreds of thousands of columns
  • Includes views via swap-in: replace sys.tables with sys.views to audit view column types instead; the rest of the join structure is identical
  • Excludes computed columns? No: sys.columns includes computed columns. To exclude them, add WHERE c.is_computed = 0. To audit only computed columns and their expressions, join to sys.computed_columns and select the definition column
  • max_length quirk: for nvarchar columns, divide max_length by 2 to get the character count. For MAX types max_length is -1 — handle this explicitly when formatting output
  • No partition awareness needed: column metadata is partition-independent, so the report represents the logical schema regardless of partitioning
  • Includes hidden columns: temporal table period columns and graph-table pseudo-columns appear unless filtered with c.is_hidden = 0

Practical Tips

  • For type-specific filtering, add a WHERE clause after the FROM/JOINs. Examples:
    • WHERE t.name = N'datetime' — every datetime column
    • WHERE t.name IN ('text','ntext','image') — every deprecated LOB column
    • WHERE t.is_user_defined = 1 — every UDT column
    • WHERE t.name = N'nvarchar' AND c.max_length = -1 — every nvarchar(MAX) column
  • For multi-database scans, wrap the query in sp_MSforeachdb or build a UNION ALL across DatabaseName.sys.columns references — the join shape stays the same
  • Combine with sys.foreign_keys to find columns whose data type doesn't match their referenced column's type, a common cause of join performance issues from implicit conversion
  • Output as CSV for a schema-diff workflow: capture the result against the source database, capture against the target, and use a row-by-row diff to surface drift
  • For exact byte counts, join c.max_length and the is_nullable flag with sys.dm_db_index_physical_stats to estimate per-row storage cost — useful when sizing a new table from existing columns
  • Add c.collation_name to the SELECT list when auditing string columns across a multi-collation database

Conclusion

This column-by-data-type query is the canonical T-SQL probe for any data-type-driven schema audit. By joining sys.columns to sys.types, sys.tables, and sys.schemas, it gives the DBA a one-screen view of every column's type signature — built-in or user-defined, system or CLR, with full precision/scale/max_length detail. Pair it with a WHERE filter for type-specific sweeps, with an export step for diffing against a target schema, or with a foreign-key join for type-mismatch detection in your referential integrity boundaries.

References

Posts in this series