Retired Features Still in Your Schema: Stretch, text and More

Retired features can sit quietly in your schema for years, and an upgrade is the day they send the bill. A short read-only inventory tells you what you have before you start.

Why old features hide so well

Imagine you inherit a database that started life many versions ago. Nobody on the team remembers the original developers. You plan an upgrade, and somebody says, “It is just a restore, right?”

Maybe. But old databases often carry old furniture: text and image columns, rules and defaults bound the old way, even metadata for a retired feature like Stretch Database. These are not all the same kind of problem. Some are deprecated, which means “please move on soon.” One of them is retired, which means “it is gone.” Each needs its own plan.

The best first step is to look. Let me build a tiny schema with a few of these, then show the queries that find them. Run it in any test database.

Build a small legacy schema

The table below has a text column, an image column and a column whose type is an alias for ntext. It also has a column with a rule and a default bound the old way, plus one ordinary DEFAULT constraint. CREATE DEFAULT and CREATE RULE must each be the first statement in their batch, which is why the script has GO lines.

DROP TABLE IF EXISTS dbo.LegacyDemo;
DROP DEFAULT IF EXISTS dbo.StatusDefault;
DROP RULE IF EXISTS dbo.StatusRule;
DROP TYPE IF EXISTS dbo.LongNote;
GO
CREATE TYPE dbo.LongNote FROM ntext NULL;
GO
CREATE TABLE dbo.LegacyDemo
(
    Id      int         NOT NULL PRIMARY KEY,
    Notes   text        NULL,
    Photo   image       NULL,
    Remarks dbo.LongNote,
    Status  varchar(10) NULL,
    Created date        NOT NULL CONSTRAINT DF_LegacyDemo_Created DEFAULT (SYSDATETIME())
);
GO
CREATE DEFAULT dbo.StatusDefault AS 'new';
GO
CREATE RULE dbo.StatusRule AS @value IN ('new', 'done');
GO
EXEC sys.sp_bindefault N'dbo.StatusDefault', N'dbo.LegacyDemo.Status';
EXEC sys.sp_bindrule N'dbo.StatusRule', N'dbo.LegacyDemo.Status';

Check the version and look for Stretch

First, write down what you are measuring. The version and compatibility level go at the top of any inventory. Compatibility level alone does not tell you which old features still work. On my SQL Server 2025 test server it shows 170.

The second query looks for tables with remote data archive turned on, which is how Stretch marks a table. It returns nothing here. If it returns rows on your server, stop and find out where that data lives before you change anything. Some of it may sit outside your server.

SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
       compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

SELECT SCHEMA_NAME(schema_id) AS SchemaName, name, is_remote_data_archive_enabled
FROM sys.tables
WHERE is_remote_data_archive_enabled = 1
ORDER BY SchemaName, name;

Find text, ntext and image columns

This query looks at the underlying type number (34 for image, 35 for text, 99 for ntext) instead of the type name. That matters because of aliases.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName,
       c.name AS ColumnName, ty.name AS DeclaredType, c.system_type_id
FROM sys.tables AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
WHERE c.system_type_id IN (34, 35, 99)
ORDER BY SchemaName, TableName, c.column_id;

You get three rows. Photo is image (34) and Notes is text (35). The third is Remarks, whose declared type is LongNote but whose real type is 99, ntext. A search by type name would have missed it. The usual replacements are varchar(max), nvarchar(max) and varbinary(max). Plan the conversion carefully, and check old drivers and procedure parameters that still expect the old types.

Find rules and defaults bound the old way

Now the bindings. An ordinary DEFAULT constraint also has a default object ID, so the join filters on parent_object_id = 0. That keeps only standalone default objects, which have no parent table. The last query checks alias types for bindings too.

SELECT OBJECT_SCHEMA_NAME(c.object_id) AS SchemaName, OBJECT_NAME(c.object_id) AS TableName,
       c.name AS ColumnName, OBJECT_NAME(c.rule_object_id) AS BoundRule, d.name AS BoundDefault
FROM sys.columns AS c
LEFT JOIN sys.objects AS d
  ON d.object_id = c.default_object_id AND d.parent_object_id = 0
WHERE c.rule_object_id <> 0 OR d.object_id IS NOT NULL
ORDER BY SchemaName, TableName, c.column_id;

SELECT name, rule_object_id, default_object_id
FROM sys.types
WHERE is_user_defined = 1 AND (rule_object_id <> 0 OR default_object_id <> 0)
ORDER BY name;

The first query finds Status, with StatusRule and StatusDefault. It does not list the Created column, because its default is an ordinary constraint. The second query returns nothing, since I bound nothing to the alias type.

Decide what to fix first

Do not treat everything you found as one problem. Stretch is retired in SQL Server 2025, while the old types and bindings are deprecated. Put blockers and data safety first, then active application code, then the rest. Test conversions with large values and NULLs. When you replace a shared default with a constraint, keep the same expression for every column that used it.

Also remember what this inventory cannot see: external scripts and SQL that applications build on the fly. A clean result is a good start, not a clean bill of health. Finally, tidy up the demo.

EXEC sys.sp_unbindefault N'dbo.LegacyDemo.Status';
EXEC sys.sp_unbindrule N'dbo.LegacyDemo.Status';
GO
DROP TABLE IF EXISTS dbo.LegacyDemo;
DROP DEFAULT IF EXISTS dbo.StatusDefault;
DROP RULE IF EXISTS dbo.StatusRule;
DROP TYPE IF EXISTS dbo.LongNote;

Before your next upgrade, run the inventory first and the plan second.

A clean schema scan is not a clean application, it is only where the review starts.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.