SQL Server Check if an index exists on table column

6rqinv9w 于 2023-03-07 发布在其他

关注(0)|答案(4)|浏览(161)

I have a index defined on a one single column of a table. I am using the following query to determine whether the index exist on one column or not. This seems to work but is there a better way to do this? I looked at other solutions on stackoverflow but that does not seem to work well.

SELECT MIN(ColumnsCount) FROM 
    (
    SELECT COUNT(*)  AS ColumnsCount, 
            SysIndex.name
    FROM sys.indexes As SysIndex
        Inner Join sys.index_columns As SysIndexCol On SysIndex.object_id = SysIndexCol.object_id And SysIndex.index_id = SysIndexCol.index_id 
        Inner Join sys.columns As SysCols On SysIndexCol.column_id = SysCols.column_id And SysIndexCol.object_id = SysCols.object_id  
    WHERE SysIndex.name 
    in 
    (
     Select 
        SysIndex.name
    From 
        sys.indexes As SysIndex
        Inner Join sys.index_columns As SysIndexCol On SysIndex.object_id = SysIndexCol.object_id And SysIndex.index_id = SysIndexCol.index_id 
        Inner Join sys.columns As SysCols On SysIndexCol.column_id = SysCols.column_id And SysIndexCol.object_id = SysCols.object_id 
    Where 
        type <> 0 
        And SysIndex.object_id in (Select systbl.object_id from sys.tables as systbl Where SysTbl.name = 'TableName')
        And SysCols.name = 'ColName'
    ) 
    GROUP BY SysIndex.name) A

sql-server

来源：https://stackoverflow.com/questions/24116418/check-if-an-index-exists-on-table-column

4条答案

按热度按时间

eyh26e7m1#

What you are retrieving is the lowest number of columns used in an index on a given table, where the index includes a given column. Your query can be simplified to:

SELECT  TOP 1 ColumnsCount = COUNT(*)
    FROM    sys.index_columns AS ic
            INNER JOIN sys.indexes AS i
                ON ic.[object_id] = i.[object_id]
                AND ic.index_id = i.index_id
            INNER JOIN sys.columns AS c
                ON ic.[object_id] = c.[object_id]
                AND ic.column_id = c.column_id
    WHERE   ic.[object_id] = OBJECT_ID(N'dbo.YourTableName')
    AND     i.[type] != 0
    AND     ic.is_included_column = 0
    GROUP BY i.index_id
    HAVING  COUNT(CASE WHEN c.Name = 'YourColumnName' THEN 1 END) > 0
    ORDER BY ColumnsCount;

I've added the condition in ic.is_included_column = 0 , on the assumption that you don't want to include non key columns in the account, nor are you interested in indexes where the given column is not a key column. If this assumption is incorrect then remove this predicate.

However, if your current query works, I don't see that there is much benefit from optimising a query on the system catalogs. They aren't likely to be performance killers.

赞(0）回复(0）举报 2023-03-07

ljo96ir52#

Not sure why such a big query but if I have understood correctly, you are trying to find whether a specific index idx is present in table X . if that's the case then you can directly query sys.indexes table like below (assuming your index name idx123 and your table name is table1 )

SELECT * 
FROM sys.indexes 
WHERE name='idx123' 
AND object_id = OBJECT_ID('table1')

赞(0）回复(0）举报 2023-03-07

nuypyhwy3#

What about this it's simple and you don't need to be root user

SHOW INDEXES FROM %TABLE_NAME_% WHERE Column_name = %COLUMN_NAME%;

赞(0）回复(0）举报 2023-03-07

ffx8fchx4#

This worked for me:

select count(*) from sys.tables as t
inner join sys.columns as c
    on  t.object_id = c.object_id
inner join sys.index_columns as ic 
    on c.column_id = ic.column_id and c.object_id = ic.object_id
inner join sys.indexes as i
    on ic.index_id = i.index_id and ic.object_id = i.object_id
where t.name = 'table_name' and c.name = 'column_name'

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Check if an index exists on table column

4条答案

相关问题

热门标签

最新问答