SQL to list all tables that does not have a particular column in their index

Question

I want to list all the tables which does not contain a particular column in their index but the column exist in the table schema.

I referenced List of all index & index columns in SQL Server DB but the query is listing all the tables with all the index.

For example, if the table schema contains a column 'Date' and the index does not contain the column 'Date' then i want to list this table in my result.

Yeah I want to check only for one specific column, I want to draft a query to List out all tables which don’t have Index with 'Date' although they have column Date, in their schema — bl1234, Jul 01 '15 at 02:42

score 1 · Accepted Answer · answered Jul 01 '15 at 02:57

DECLARE @search_column SYSNAME;

SET @search_column = 'Date';

SELECT s.name AS [Schema], t.name AS [Table]
FROM
    sys.schemas s
        INNER JOIN sys.tables t ON
            t.[schema_id] = s.[schema_id]
        INNER JOIN sys.columns c ON
            c.[object_id] = t.[object_id]
WHERE
    c.name = @search_column AND
    NOT EXISTS
    (
        SELECT *
        FROM
            sys.indexes i
                INNER JOIN sys.index_columns ic ON
                    ic.[object_id] = i.[object_id] AND
                    ic.index_id = i.index_id
        WHERE
            i.[object_id] = t.[object_id] AND
            ic.column_id = c.column_id
    )
ORDER BY
    s.name,
    t.name;

SQL to list all tables that does not have a particular column in their index

1 Answers1