If you want to modify a column in an existing SQL Server database, but there is a View using that column, you will find you are unable to modify the column. You will have to drop the view, modify the column, then recreate the view. That's fine in principle, but if you have a complex database how can you find all views which depend on the database column? To further complicate matters, some views may not directly use the column, but may depend on a view which does.

I recently faced this scenario, so I asked ChatGPT to write a SQL script to find the dependent Views. The SQL it generated didn't run, but it was close enough that I was able to modify it to create a script which was good enough for my purposes.

/* Enter details of a table column which may be used by Views. 
 * This SQL will list all Views which use the column (directly or indirectly), 
 * and will show the order in which you should drop the Views in order to modify the column.
 * A higher DropOrder means you should drop those Views first.
 */
DECLARE @ColumnDetails TABLE (
    SchemaName NVARCHAR(MAX),
    TableName NVARCHAR(MAX),
    ColumnName NVARCHAR(MAX)
);

INSERT INTO @ColumnDetails (SchemaName, TableName, ColumnName)
VALUES 
    ('Table1Schema', 'Table1Name', 'ColumnWithinTable1'),
    ('Table2Schema', 'Table2Name', 'ColumnWithinTable2');

WITH ViewHierarchy AS (
    SELECT 
        v.object_id AS ViewId,
        0 AS Depth
    FROM sys.views v
    JOIN sys.schemas s
        ON v.schema_id = s.schema_id
    JOIN sys.sql_dependencies d 
        ON v.object_id = d.object_id
    JOIN sys.columns c 
        ON d.referenced_major_id = c.object_id 
        AND d.referenced_minor_id = c.column_id
    JOIN sys.tables t 
        ON c.object_id = t.object_id
    JOIN @ColumnDetails cd
        ON t.schema_id = SCHEMA_ID(cd.SchemaName)
        AND t.name = cd.TableName
        AND c.name = cd.ColumnName
    UNION ALL
    SELECT
        v.object_id AS ViewId,
        vh.Depth + 1
    FROM sys.views v
    JOIN sys.schemas s
        ON v.schema_id = s.schema_id
    JOIN sys.sql_dependencies d 
        ON v.object_id = d.object_id
    JOIN ViewHierarchy vh 
        ON d.referenced_major_id = vh.ViewId
)
SELECT DISTINCT
    s.name + '.' + v.name AS ViewName,
    m.definition AS ViewDefinition,
    vh.Depth AS DropOrder
FROM sys.views v
JOIN sys.schemas s
    ON v.schema_id = s.schema_id
JOIN sys.sql_modules m 
    ON v.object_id = m.object_id
JOIN ViewHierarchy vh 
    ON v.object_id = vh.ViewId
ORDER BY vh.Depth DESC, s.name + '.' + v.name ASC;

You will also be unable to modify a column which is used in an index, so this script finds those dependencies:

DECLARE @ColumnDetails TABLE (
    SchemaName NVARCHAR(MAX),
    TableName NVARCHAR(MAX),
    ColumnName NVARCHAR(MAX)
);

INSERT INTO @ColumnDetails (SchemaName, TableName, ColumnName)
VALUES 
    ('Table1Schema', 'Table1Name', 'ColumnWithinTable1'),
    ('Table2Schema', 'Table2Name', 'ColumnWithinTable2');

-- Find indexes that depend on any of the columns
SELECT DISTINCT
    schema_name(t.schema_id) + '.' + t.name AS TableContainingIndex,
    i.name AS IndexName
FROM sys.tables t
JOIN sys.columns c 
    ON t.object_id = c.object_id
JOIN sys.index_columns ic 
    ON t.object_id = ic.object_id 
    AND c.column_id = ic.column_id
JOIN sys.indexes i 
    ON ic.object_id = i.object_id 
    AND ic.index_id = i.index_id
WHERE EXISTS (
    SELECT 1
    FROM @ColumnDetails id
    WHERE id.SchemaName = schema_name(t.schema_id)
        AND id.TableName = t.name
        AND id.ColumnName = c.name
);

Be aware that I have not tested these scripts extensively, so there may be edge cases which they get wrong.