Find all tables that have ROWGUIDCOL

Question

IS there a system table I can join to so I can query to find all tables with a column flagged as ROWGUIDCOL?

thanks!

Answer 1

You can use this sql query to achive your goal. Objects.Type = 'U' is user table.

SELECT O.name AS table_name,
       C.name AS column_name
FROM sys.objects AS O
    JOIN sys.columns AS C ON C.object_id = O.object_id
WHERE o.type = 'U'
        AND C.is_rowguidcol = 1

Answer 2

You could utilize sys.columns with COLUMNPROPERTY:

SELECT DISTINCT OBJECT_NAME(object_id) AS tab_name
FROM sys.columns
WHERE COLUMNPROPERTY(object_id, name, 'IsRowGuidCol') = 1

SqlFiddleDemo

---

CREATE TABLE MyTable(ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID());
CREATE TABLE MyTable2(ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY 
                      DEFAULT NEWSEQUENTIALID());

Output:

╔══════════╗
║ tab_name ║
╠══════════╣
║ MyTable2 ║
╚══════════╝

Answer 3

You can use COLUMNPROPERTY() like so:

SELECT COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), c.COLUMN_NAME, 'IsRowGuidCol') 
AS IsRowGuidCol , *
FROM INFORMATION_SCHEMA.COLUMNS AS C
WHERE COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), c.COLUMN_NAME, 'IsRowGuidCol') 
= 1