SQL SERVER – Checking Column Existence Is Different from DROP IF EXISTS

I use COL_LENGTH to Check If a Column Exists in visible metadata. Conditional DROP instead performs a deletion.

CREATE TABLE #ColumnExample (ID int,OptionalColumn int);
IF COL_LENGTH(N'tempdb..#ColumnExample',N'OptionalColumn') IS NOT NULL
BEGIN
 SELECT N'Column exists' AS Result;
END
ELSE
BEGIN
 SELECT N'Column not visible or absent' AS Result;
END;
-- SQL Server 2016+: this deletes a column, it is not a read-only check.
ALTER TABLE #ColumnExample DROP COLUMN IF EXISTS OptionalColumn;
SELECT name FROM tempdb.sys.columns WHERE object_id=OBJECT_ID(N'tempdb..#ColumnExample');
DROP TABLE #ColumnExample;

My original IF block lacked END before ELSE. The complete temporary-table example confines deletion to its own object. Qualify a business-table name correctly. Perform only the intended operation.

COL_LENGTH returns defined byte length or NULL when metadata is unavailable, absent or affected by another relevant error. Permission limits can produce NULL. A separate check and action can also race with another session.

Conditional DROP syntax varies by object type. Dependencies can still prevent deletion. It does not preserve references or constraints automatically. Keep working checks that satisfy the actual requirement.

Reference: Column metadata visibility and length.

Related reading

• SQL in Sixty Seconds series
• MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
• Tuning Query Cost 100% – SQL in Sixty Seconds #181
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

A NULL metadata result is not always proof of absence, it is a result requiring context and permission checks.

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