如何标识视图中的主键列

Question

我过去常常使用' GetSchemaTable‘来读取模式信息，但它缺少一些’东西‘，所以我编写了一个大型查询，在其他列中引用sys.columns、sys.index_columns和sys.indexes (以及其他表)，以返回我用来从GetSchemaTable获取的相同信息，并返回我需要的其他信息。

问题是GetSchemaTable会告诉我从视图返回的列是否是基础表中的键列，但我的新查询不会。对于表，它会给我一整天的正确答案，但对于视图，它不会给我正确的答案。

有谁有解决这个问题的办法吗？当我检查一个视图时，我讨厌仅仅为了这一点信息而不得不回到GetSchemaTable。(另外，理想情况下，我真的只想要一个基于SQL的解决方案。)

谢谢!

Answer 1

不幸的是，在SQL Server2005中，这并不是很容易。我已经尝试过这一点，它非常接近，但它依赖于这样一个事实，即您在视图中对列的命名与它们在基表中的命名完全相同。这是因为现在已经弃用的SQL-Server-2008视图sys.sql_dependencies没有正确地存储引用column_id，因此没有办法将其与视图中的实际列相匹配。我认为SQL Server2008将为您提供更好的选择，因为它们再次引入了一组新的依赖对象。我也没有使用INFORMATION_SCHEMA.KEY_COLUMN_USAGE，但是由于这些视图完全依赖于名称，而不是任何类型的id，所以您很可能也处于同样的困境中。因此，这可能是您的一个开始，但正如我所说的，这将只涵盖简单的情况。如果你给你的列加上别名，你就不走运了。也许其他对这些事物如何引用的错综复杂有一定洞察力的人会找出一只兔子，并弄清楚如何引用不匹配的列……

-- very simple; one-column key:

CREATE TABLE dbo.boo
(
    far INT PRIMARY KEY
);
GO

CREATE VIEW dbo.view_boo
AS
    SELECT far FROM dbo.boo;
GO

-- slightly more complex.  Two-column key,
-- not all columns are in key, view columns
-- are in different order:

CREATE TABLE dbo.foo
(
    splunge INT,
    a INT,
    mort INT,
    PRIMARY KEY(splunge, mort)
);
GO

CREATE VIEW dbo.view_foo
AS
    SELECT
        splunge,
        mort,
        a
    FROM
        dbo.foo;
GO

SELECT 
    QUOTENAME(OBJECT_SCHEMA_NAME(v.[object_id])) + '.' 
    + QUOTENAME(v.name) + '.' + QUOTENAME(vc.name) 
    + ' references ' 
    + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
    + '.' + QUOTENAME(t.name) + '.' + QUOTENAME(tc.name)
FROM 
    sys.views AS v
INNER JOIN
    sys.sql_dependencies AS d
    ON v.[object_id] = d.[object_id]
INNER JOIN
    sys.tables AS t
    ON d.referenced_major_id = t.[object_id]
INNER JOIN
    sys.columns AS tc
    ON tc.[object_id] = t.[object_id]
INNER JOIN
    sys.index_columns AS ic
    ON tc.[object_id] = ic.[object_id]
    AND tc.column_id = ic.column_id
    AND tc.column_id = d.referenced_minor_id
INNER JOIN
    sys.columns AS vc
    ON vc.[object_id] = v.[object_id]
    AND vc.name = tc.name -- the part I don't like
INNER JOIN
    sys.indexes AS i
    ON ic.[object_id] = i.[object_id]
    AND i.is_primary_key = 1
ORDER BY
    t.name,
    ic.key_ordinal;

GO

DROP VIEW dbo.view_boo, dbo.view_foo;
DROP TABLE dbo.foo, dbo.boo;