How can you identify the PK columns in a View.

Question

I used to use 'GetSchemaTable' to read schema information, but it was missing some 'stuff', so I wrote a big query, referencing, among other columns, sys.columns,sys.index_columns, and sys.indexes (and other tables) to return the same information I used to get from GetSchemaTable and also return the other pieces of information I want.

Problem is that GetSchemaTable will tell me if a column returned from a view is a Key column from the underlying tables but my new query does not. It'll give me the right answer all day long for tables, but not for views.

Does anyone have a solution to this? I'd hate to have to go back to GetSchemaTable just for that one bit of information, when I'm examing a view. (Plus, I really just want a SQL based solution, ideally.)

Thanks!

Answer 1

Unfortunately in SQL Server 2005 this is not very easy. I have played with this a bit, and it is very close, but it relies on the fact that you name your columns in your view exactly the same as they are named in the base table. This is because the now-deprecated-in-SQL-Server-2008 view sys.sql_dependencies does not properly store the referencing column_id, so there is no way to match this up with the actual columns in the view. I think SQL Server 2008 will have better options for you as they have yet again introduced a new set of dependency objects. I also didn't chase down any paths with INFORMATION_SCHEMA.KEY_COLUMN_USAGE but since these views rely solely on names and not id's of any kind you are likely in the same pickle there. So maybe this can be a start for you but like I said this will only cover the simple cases. If you alias your columns you will be out of luck. Maybe someone else with some insight into the intricacies of how these things are referenced will pull a rabbit out and figure out how to reference mismatched columns...

-- 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;