--1st set
SELECT
                    TABLE_DATA.name AS TableName
                  , INDEX_DATA.name AS IndexName
				  , INDEX_DATA.type_desc AS IndexType
                  , STUFF(( SELECT  ', ' + COLUMN_DATA_KEY_COLS.name
                            FROM    sys.tables AS T
                                    INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id
                                    INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
                                                              AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
                                    INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
                                                              AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
                            WHERE   INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
                                    AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
                                    AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
                            ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
                          FOR
                            XML PATH('')
                          ), 1, 2, '') AS ColumnLists
                  , STUFF(( SELECT  ', ' + COLUMN_DATA_INC_COLS.name
                            FROM    sys.tables AS T
                                    INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id
                                    INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
                                                              AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
                                    INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id
                                                              AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
                            WHERE   INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
                                    AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
                                    AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
                            ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
                          FOR
                            XML PATH('')
                          ), 1, 2, '') AS IncludedColumns
FROM   sys.indexes INDEX_DATA
	INNER JOIN sys.tables TABLE_DATA 
		ON TABLE_DATA.object_id = INDEX_DATA.object_id
    INNER JOIN sys.schemas SCHEMA_DATA 
		ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
WHERE  TABLE_DATA.is_ms_shipped = 0
	AND INDEX_DATA.type_desc IN ( 'NONCLUSTERED', 'CLUSTERED' ) -- Avoiding heaps
	AND INDEX_DATA.name='IndexABC' 
	AND TABLE_DATA.name='MyTable' 

--2nd set
SELECT TableName, IndexName, IndexType, ColumnLists, IncludedColumns
FROM DROPPED
WHERE IndexName='IndexABC' 
	AND TableName='MyTable'

select count(*) from A -- should be the same as below
select count(*) from B -- should be the same as above
select * from A except select * from B -- should return no rows
select * from B except select * from A -- should return no rows

quote:

---

Originally posted by gbritton

Using set algebra, if A-B = B-A = the empty set, the sets are equal. In SQL we have to consider the number of rows, since SQL tables are multisets or bags.

select count(*) from A -- should be the same as below
select count(*) from B -- should be the same as above
select * from A except select * from B -- should return no rows
select * from B except select * from A -- should return no rows

---

Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the EXCEPT operation.