ALTER TABLE ALTER COLUMN failed because one or more objects access this column

Recently I ran across an issue with manually created statistics objects preventing datatype modification for a column from varchar to nvarchar. This issue occurred because the client has a policy in place where auto create statistics is disabled by default in all SQL Server databases, necessitating the manual creation of statistics objects where the query optimizer requires them for good performance.

When attempting to modify a column that has a manually created statistics object attached, you receive the following error message:

Msg 5074, Level 16, State 1, Line 36
The statistics '<name>' is dependent on column '<col>'.
Msg 4922, Level 16, State 9, Line 36
ALTER TABLE ALTER COLUMN <col> failed because one or more objects access this column.

Interestingly, if SQL Server has auto-created a stats object on a column, and you subsequently modify that column, you receive no such error. SQL Server silently drops the statistics object, and modifies the column. The auto-created stats object is *not* automatically recreated until a query is executed that needs the stats object. This difference in how auto-created stats and manually created stats are treated by the engine can make for some confusion.

Consider the following minimally complete and verifiable example code that can be used to repro the problem:

SET NOCOUNT ON;
USE master;
GO
--Create a new, blank database for our test
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'test_stats_alter')
BEGIN
    ALTER DATABASE test_stats_alter SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE test_stats_alter;
END
CREATE DATABASE test_stats_alter;
ALTER DATABASE test_stats_alter SET AUTO_CREATE_STATISTICS OFF;
ALTER DATABASE test_stats_alter SET AUTO_UPDATE_STATISTICS OFF;
GO

USE test_stats_alter;
GO
CREATE TABLE dbo.stats_test
(
    id int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , d varchar(30) NOT NULL
);
CREATE TABLE dbo.dates
(
    d varchar(30) NOT NULL
)
GO
--Insert a bunch of rows to allow the query optimizer to perform actual work.
INSERT INTO dbo.stats_test (d)
SELECT CONVERT(datetime, DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), '1900-01-01T00:00:00'))
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2

INSERT INTO dbo.dates (d)
SELECT CONVERT(datetime, DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), '1900-01-01T00:00:00'))
FROM sys.syscolumns sc1
GO


--Manually create a stats object
CREATE STATISTICS stats_test_st1 
ON dbo.stats_test(d)
WITH FULLSCAN, NORECOMPUTE;
GO

--Attempt to alter the column with the manual stats object defined.
--This will fail with Msg 5074, Level 16, State 1, Line xx
ALTER TABLE dbo.stats_test
ALTER COLUMN d nvarchar(30) NOT NULL;
GO

SQL Server returns this error:

Msg 5074, Level 16, State 1, Line 47
The statistics 'stats_test_st1' is dependent on column 'd'.
Msg 4922, Level 16, State 9, Line 47
ALTER TABLE ALTER COLUMN d failed because one or more objects access this column.

Let’s continue on:

--drop the stats object
DROP STATISTICS dbo.stats_test.stats_test_st1;
GO

--Allow SQL Server to automatically create statistics
ALTER DATABASE test_stats_alter SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE test_stats_alter SET AUTO_UPDATE_STATISTICS ON;
GO

--Coerce SQL Server into automatically creating a stats object.
--This is a complex enough query that SQL Server recognizes a
--stats object would be helpful for good performance.
SELECT st.id
    , st.d
INTO dbo.stats_test_output
FROM dbo.stats_test st
    LEFT JOIN dbo.dates d ON st.d = d.d
WHERE st.d > '2017-06-01T00:00:00';
GO

--See if SQL Server in fact created an auto-stats object on 
--the column.
SELECT *
FROM sys.stats st
    INNER JOIN sys.objects o ON st.object_id = o.object_id
WHERE o.name = 'stats_test';
GO
╔════════════╦════════════════════════════════╦══════════════╗
║    name    ║              name              ║ auto_created ║
╠════════════╬════════════════════════════════╬══════════════╣
║ stats_test ║ PK__stats_te__3213E83FF58F8430 ║            0 ║
║ stats_test ║ _WA_Sys_00000002_21B6055D      ║            1 ║
╚════════════╩════════════════════════════════╩══════════════╝
--attempt to alter the table, which succeeds.
ALTER TABLE dbo.stats_test
ALTER COLUMN d nvarchar(30) NOT NULL;
GO

--check to see if the auto-created stats object still exists
SELECT *
FROM sys.stats st
    INNER JOIN sys.objects o ON st.object_id = o.object_id
WHERE o.name = 'stats_test';

The auto-created stats object has been silently dropped:

╔════════════╦════════════════════════════════╦══════════════╗
║    name    ║              name              ║ auto_created ║
╠════════════╬════════════════════════════════╬══════════════╣
║ stats_test ║ PK__stats_te__3213E83FF58F8430 ║            0 ║
╚════════════╩════════════════════════════════╩══════════════╝

If you have auto_create_statistics turned off, you probably want to update your stats objects on a regular basis to ensure good performance. See my previous post for details about how to do that.