list columns with identity, primary key and foreign key flags

SELECT   c.TABLE_CATALOG                            AS DatabaseName,

         c.TABLE_SCHEMA                             AS SchemaName,

         c.TABLE_NAME                               AS TableName,

         c.COLUMN_NAME                              AS ColumnName,

         Columnproperty(Object_id(c.TABLE_SCHEMA + '.' + c.TABLE_NAME),

                        c.COLUMN_NAME,'ISIDENTITY') AS IsIdentity,

         CASE

           WHEN CONSTRAINT_NAME IN (SELECT NAME

                                    FROM   sys.objects

                                    WHERE  TYPE = 'PK') THEN 1

           ELSE 0

         END AS IsPrimaryKey,

         CASE

           WHEN CONSTRAINT_NAME IN (SELECT NAME

                                    FROM   sys.objects

                                    WHERE  TYPE = 'F') THEN 1

           ELSE 0

         END AS IsForeignKey

FROM     INFORMATION_SCHEMA.TABLES t

         INNER JOIN INFORMATION_SCHEMA.COLUMNS c

             ON c.TABLE_CATALOG = t.TABLE_CATALOG

              AND c.TABLE_SCHEMA = t.TABLE_SCHEMA

              AND c.TABLE_NAME = t.TABLE_NAME

         LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE u

           ON c.TABLE_CATALOG = u.TABLE_CATALOG

              AND c.TABLE_SCHEMA = u.TABLE_SCHEMA

              AND c.TABLE_NAME = u.TABLE_NAME

              AND c.COLUMN_NAME = u.COLUMN_NAME

WHERE TABLE_TYPE='BASE TABLE'

ORDER BY SchemaName,

         TableName,

         c.ORDINAL_POSITION

GO