TugboatAIRebuild/DataMigration/GetForeignKeyInfo.sql

-- SQL Server 查询外键和索引信息的脚本

-- 查询所有外键信息
SELECT 
    fk.name AS ForeignKeyName,
    t1.name AS TableName,
    c1.name AS ColumnName,
    t2.name AS ReferencedTableName,
    c2.name AS ReferencedColumnName
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.tables AS t1 ON fkc.parent_object_id = t1.object_id
INNER JOIN sys.columns AS c1 ON fkc.parent_object_id = c1.object_id AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.tables AS t2 ON fkc.referenced_object_id = t2.object_id
INNER JOIN sys.columns AS c2 ON fkc.referenced_object_id = c2.object_id AND fkc.referenced_column_id = c2.column_id;

-- 查询所有表的索引信息
SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    c.name AS ColumnName,
    ic.index_column_id AS ColumnPosition,
    i.is_unique AS IsUnique,
    i.is_primary_key AS IsPrimaryKey
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.type > 0  -- 忽略堆表（无聚集索引的表）
ORDER BY t.name, i.name, ic.index_column_id;

-- 查询主键信息（作为参考）
SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    i.name AS IndexName
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.is_primary_key = 1
ORDER BY t.name, c.name;