Querying Table Definitions

A query to return metadata about a table. This is useful for dynamic query building like in a SqlAdapter, for example.

SELECT C.COLUMN_NAME,
       C.DATA_TYPE,
       C.CHARACTER_MAXIMUM_LENGTH,
       CASE WHEN PK.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END IS_KEY,
       CASE WHEN IC.object_id IS NULL THEN 0 ELSE 1 END IS_IDENTITY
FROM INFORMATION_SCHEMA.COLUMNS C
       LEFT OUTER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK ON C.TABLE_NAME = PK.TABLE_NAME
                                      AND C.COLUMN_NAME = PK.COLUMN_NAME
       INNER JOIN
sys.tables T ON C.TABLE_NAME = T.name
            AND T.[type] = 'U'
       LEFT OUTER JOIN
sys.identity_columns IC ON T.object_id = IC.object_id
                       AND IC.[name] = C.COLUMN_NAME
WHERE C.TABLE_NAME = @TableName