Thursday, July 15, 2010

Find columns with a particular property

Here's a query I found handy when I needed to retrieve a list of columns from any table in a database that is a ROWGUID column.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMNPROPERTY
(
OBJECT_ID(QUOTENAME(table_schema) + '.' +
QUOTENAME(table_name)) -- table ID
,column_name
,'IsRowGuidCol'
) = 1
ORDER BY table_name, ordinal_position

You can also replace 'IsRowGuidCol' with any other property listed here.