SQL Server 2025 sp_columns Omits JSON and Vector Columns
Schema-discovery code still uses sp_columns, even though it is an older compatibility procedure. sp_columns is there on every SQL Server instance, Open Database Connectivity (ODBC) catalog calls line up with it, and catalog APIs are commonly used to get column metadata before a statement runs.
This post is part of the Undocumented Changes in SQL Server 2022 and 2025 series. The contents page lists every part in order.
In the SQL Server 2025 changed-procedures post, I called out a new sp_columns filter for json and vector columns. This post tests that one change on a real table. It also links back to the first post on SQL Server 2025, because the same inventory work found this change.
The short version: on my SQL Server 2025 test instance, sp_columns returned the int and nvarchar columns from my test table. It did not return the json or vector(3) columns. The ODBC catalog path I tested through System.Data.Odbc returned the same two-column list. INFORMATION_SCHEMA.COLUMNS, sys.columns, and sp_describe_first_result_set all returned four columns from the same table, which is why I treat them as alternatives below.
The filter in the procedure text
The SQL Server 2025 change adds a type-code predicate to two result queries. In SQL Server 2022 Cumulative Update (CU) 25, these two result queries in sys.sp_columns ended after the sparse-column predicate:
|
1 2 3 4 5 6 7 8 9 10 11 |
-- SQL Server 2022 CU25, sys.sp_columns and s_cov.ODBCVER = @ODBCVer and s_cov.OBJECT_TYPE <> 'TT' and ( s_cov.SS_IS_SPARSE = 0 OR objectproperty ( s_cov.OBJECT_ID, 'tablehascolumnset' ) = 0 ) order by 17 -- Later in the same procedure (@column_name is null or s_cov.COLUMN_NAME like @column_name) and ( s_cov.SS_IS_SPARSE = 0 OR objectproperty ( s_cov.OBJECT_ID, 'tablehascolumnset' ) = 0 ) order by 2, 3, 17 |
In SQL Server 2025 CU9, those same two paths add a type-code filter:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- SQL Server 2025 CU9, sys.sp_columns and s_cov.ODBCVER = @ODBCVer and s_cov.OBJECT_TYPE <> 'TT' and ( s_cov.SS_IS_SPARSE = 0 OR objectproperty ( s_cov.OBJECT_ID, 'tablehascolumnset' ) = 0 ) and s_cov.DATA_TYPE_28 not in (-156,-157) -- exclude vector and json since these are new types order by 17 -- Later in the same procedure (@column_name is null or s_cov.COLUMN_NAME like @column_name) and ( s_cov.SS_IS_SPARSE = 0 OR objectproperty ( s_cov.OBJECT_ID, 'tablehascolumnset' ) = 0 ) and s_cov.DATA_TYPE_28 not in (-156,-157) -- exclude vector and json since these are new types order by 2, 3, 17 |
The sibling procedures that changed in the same family were sys.sp_columns_100 and sys.sp_columns_90. I did not find changed object_hash rows for sp_columns_ex, sp_columns_ex_100, sp_columns_ex_90, or sp_columns_managed in the 2022-to-2025 diff. SQL Server 2025 also has a new sys.sp_columns_170 definition in my inventory.
The same filter appears in the two changed sibling procedures, with the version-specific type column:
|
1 2 3 4 5 |
-- SQL Server 2025 CU9, sys.sp_columns_100 and s_cov.DATA_TYPE not in (-156,-157) -- exclude vector and json since these are new types -- SQL Server 2025 CU9, sys.sp_columns_90 and s_cov.DATA_TYPE_90 not in (-156,-157) -- exclude vector and json since these are new types |
The procedure comment is the only explanation I found for the filter. The code says, exclude vector and json since these are new types.
The public sp_columns page says the procedure returns column information for objects that can be queried in the current environment. It also says the sp_columns catalog stored procedure is equivalent to ODBC SQLColumns.[1] I did not find a note on that page about excluding json or vector columns. Microsoft documents the SQL Server 2025 json data type and the SQL Server 2025 vector data type on separate pages.[2][3]
The table I used
I used a scratch database whose name starts with undoc_spinoff_, per the rules for these posts. The table has int, nvarchar, json, and vector(3) columns.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SET NOCOUNT ON; IF DB_ID(N'undoc_spinoff_spcolumns') IS NULL BEGIN CREATE DATABASE undoc_spinoff_spcolumns; END; GO USE undoc_spinoff_spcolumns; GO DROP TABLE IF EXISTS dbo.catalog_probe; GO CREATE TABLE dbo.catalog_probe ( probe_id int NOT NULL , description nvarchar(100) NULL , payload json NULL , embedding vector(3) NULL ); GO |
The json documentation shows this column syntax: column_name JSON [ NOT NULL | NULL ]. The vector documentation shows this syntax: column_name VECTOR ( { <dimensions> } ) [ NOT NULL | NULL ], and its examples use VECTOR(3) for a three-dimension vector.[4][5]
What each method returned
I ran five discovery paths against that table. This is the result, reduced to the column names because that is the part a schema-discovery tool usually needs first.
| Method | Returned columns | Missing from returned list |
|---|---|---|
EXEC sys.sp_columns |
probe_id, description |
payload, embedding |
ODBC GetSchema('Columns') with ODBC Driver 18.6.2.1 |
probe_id, description |
payload, embedding |
INFORMATION_SCHEMA.COLUMNS |
probe_id, description, payload, embedding |
None |
sys.columns joined to sys.types |
probe_id, description, payload, embedding |
None |
sys.sp_describe_first_result_set |
probe_id, description, payload, embedding |
None |
Here is the direct sp_columns call:
|
1 2 3 4 5 6 |
USE undoc_spinoff_spcolumns; GO EXEC sys.sp_columns @table_name = N'catalog_probe' , @table_owner = N'dbo'; |
The output had two rows:
|
1 2 3 4 |
COLUMN_NAME DATA_TYPE TYPE_NAME ORDINAL_POSITION ----------- --------- --------- ---------------- probe_id 4 int 1 description -9 nvarchar 2 |
I also called sp_columns with @ODBCVer = 3, and it returned the same two rows. That matches the procedure text: both main result queries have the new not in (-156,-157) predicate.
The information schema view returned all four rows:
|
1 2 3 4 5 |
SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'catalog_probe' ORDER BY ORDINAL_POSITION; |
|
1 2 3 4 5 6 |
ORDINAL_POSITION COLUMN_NAME DATA_TYPE IS_NULLABLE ---------------- ----------- --------- ----------- 1 probe_id int NO 2 description nvarchar YES 3 payload json YES 4 embedding vector YES |
sys.columns also returned all four rows, and it gave extra vector metadata:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT c.column_id , c.name AS column_name , t.name AS type_name , c.system_type_id , c.user_type_id , c.max_length , c.vector_dimensions , c.vector_base_type_desc FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id = t.user_type_id WHERE c.object_id = OBJECT_ID(N'dbo.catalog_probe') ORDER BY c.column_id; |
|
1 2 3 4 5 6 |
column_id column_name type_name system_type_id user_type_id max_length vector_dimensions vector_base_type_desc --------- ----------- --------- -------------- ------------ ---------- ----------------- --------------------- 1 probe_id int 56 56 4 NULL NULL 2 description nvarchar 231 231 200 NULL NULL 3 payload json 244 244 -1 NULL NULL 4 embedding vector 165 255 20 3 float32 |
The sys.columns page says it returns one row for each column of an object that has columns. The current page also documents vector_dimensions, vector_base_type, and vector_base_type_desc for SQL Server 2025 and later.[6]
What ODBC saw
I tested the client catalog path with System.Data.Odbc and Microsoft ODBC Driver 18 for SQL Server. The 64-bit driver file was msodbcsql18.dll, product version 18.6.2.1. I opened a trusted local connection to the scratch database and called GetSchema('Columns'), then filtered the returned table to dbo.catalog_probe.
|
1 2 3 4 5 6 7 8 9 |
$driver = 'ODBC Driver 18 for SQL Server' $cs = "Driver={$driver};Server=.\SQL2025;Database=undoc_spinoff_spcolumns;Trusted_Connection=Yes;TrustServerCertificate=Yes;Encrypt=Optional;" $conn = [System.Data.Odbc.OdbcConnection]::new($cs) $conn.Open() $schema = $conn.GetSchema('Columns') $schema | Where-Object { $_.TABLE_NAME -eq 'catalog_probe' } | Sort-Object { [int]$_.ORDINAL_POSITION } | Select-Object TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, ORDINAL_POSITION, IS_NULLABLE $conn.Close() |
I sanitized the server name in the sample above. The returned rows matched sp_columns:
|
1 2 3 4 |
COLUMN_NAME DATA_TYPE TYPE_NAME ORDINAL_POSITION IS_NULLABLE ----------- --------- --------- ---------------- ----------- probe_id 4 int 1 NO description -9 nvarchar 2 YES |
The ODBC SQLColumns documentation says the function returns the list of column names in specified tables, as a result set. It also says SQLColumns might not return all columns.[7] In this test, a client catalog call did not see the two new-type columns.
One alternative has its own type-mapping caveat
sp_describe_first_result_set answered a different question. It described the first result set of a query, not the stored table definition. It returned all four selected columns:
|
1 2 3 4 |
EXEC sys.sp_describe_first_result_set @tsql = N'SELECT probe_id, description, payload, embedding FROM dbo.catalog_probe;', @params = NULL, @browse_information_mode = 0; |
For the two new types, it returned varchar(max) with a UTF-8 collation:
|
1 2 3 4 |
name system_type_id system_type_name max_length collation_name ----------- -------------- ---------------- ---------- -------------------------- payload 167 varchar(max) -1 Latin1_General_100_BIN2_UTF8 embedding 167 varchar(max) -1 Latin1_General_100_BIN2_UTF8 |
Microsoft documents this limitation for json. The json data type page says sp_describe_first_result_set doesn’t correctly return the json data type, and many clients and drivers see varchar or nvarchar instead.[8] The vector page also says older clients can work with vector data as a JSON array exposed as varchar(max).[9]
What I would use instead
If the job is table schema discovery, I would start with sys.columns joined to sys.types. It returned all four columns, and it exposed vector-specific metadata that the other tested paths did not return. INFORMATION_SCHEMA.COLUMNS returned all four column names too, but its own Learn page warns that information schema views only represent a subset of object metadata, and says the reliable way to find an object’s schema is sys.objects.[10]
If the job is result-set discovery for a query that a tool is about to run, sp_describe_first_result_set can still be useful. It returned all four projected column names in this test. I would not use it as proof of the base table type for json or vector, because the json page documents a type-mapping limitation and the vector compatibility story can also expose data as varchar(max).
If a tool depends on sp_columns or an ODBC catalog call, the upgrade test is straightforward: create a table with json and vector columns in a scratch database, then compare the tool’s discovered schema with sys.columns. If catalog results omit payload or embedding, compare them with sys.columns before concluding that the table definition is incorrect.
Have you seen a schema-discovery tool miss SQL Server 2025 columns after an upgrade? Tell me on Bluesky or LinkedIn, and I’ll update the notes.
References
- sys.sp_columns (Transact-SQL) - Microsoft Learn. Documents the procedure, its result set, and the statement that
sp_columnsis equivalent to ODBCSQLColumns. ↩ - JSON Data Type - Microsoft Learn. Documents the SQL Server 2025
jsondata type. ↩ - Vector Data Type - Microsoft Learn. Documents the SQL Server 2025
vectordata type. ↩ - JSON Data Type - Microsoft Learn. Documents
jsoncolumn syntax. ↩ - Vector Data Type - Microsoft Learn. Documents
vector(3)column syntax. ↩ - sys.columns (Transact-SQL) - Microsoft Learn. Documents that
sys.columnsreturns one row for each column of an object and includes SQL Server 2025 vector metadata columns. ↩ - SQLColumns Function - Microsoft Learn. Documents the ODBC catalog function and notes that
SQLColumnsmight not return all columns. ↩ - JSON Data Type - Microsoft Learn. Documents the
sp_describe_first_result_settype-mapping limitation forjson. ↩ - Vector Data Type - Microsoft Learn. Documents client compatibility for vector data. ↩
- COLUMNS (Transact-SQL) - Microsoft Learn. Documents
INFORMATION_SCHEMA.COLUMNSand its metadata-subset warning. ↩