How to list columns with identity, primary key and foreign key flags?

Execute the following Microsoft SQL Server T-SQL script for listing table column meta data including column names, TRUE/FALSE (1/0) flags for IsIdentity, IsPrimaryKey and IsForeignKey properties.

-- SQL table column meta data - SQL table columns - int identity - Primary Key constraints

-- SQL ISPRIMARYKEY - SQL ISFOREIGNKEY - SQL ISIDENTITY - SQL INFORMATION_SCHEMA views

-- SQL columnproperty - SQL data dictionary - Foreign Key constraints

USE AdventureWorks2008;

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

-- Partial results 

DatabaseName	SchemaName	TableName	ColumnName	IsIdentity	IsPrimaryKey	IsForeignKey
AdventureWorks2008	Sales	SalesOrderDetail	SalesOrderID	0	1	0
AdventureWorks2008	Sales	SalesOrderDetail	SalesOrderID	0	0	1
AdventureWorks2008	Sales	SalesOrderDetail	SalesOrderDetailID	1	1	0
AdventureWorks2008	Sales	SalesOrderDetail	CarrierTrackingNumber	0	0	0
AdventureWorks2008	Sales	SalesOrderDetail	OrderQty	0	0	0
AdventureWorks2008	Sales	SalesOrderDetail	ProductID	0	0	1
AdventureWorks2008	Sales	SalesOrderDetail	SpecialOfferID	0	0	1
AdventureWorks2008	Sales	SalesOrderDetail	UnitPrice	0	0	0
AdventureWorks2008	Sales	SalesOrderDetail	UnitPriceDiscount	0	0	0
AdventureWorks2008	Sales	SalesOrderDetail	LineTotal	0	0	0
AdventureWorks2008	Sales	SalesOrderDetail	rowguid	0	0	0
AdventureWorks2008	Sales	SalesOrderDetail	ModifiedDate	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	SalesOrderID	1	1	0
AdventureWorks2008	Sales	SalesOrderHeader	RevisionNumber	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	OrderDate	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	DueDate	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	ShipDate	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	Status	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	OnlineOrderFlag	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	SalesOrderNumber	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	PurchaseOrderNumber	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	AccountNumber	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	CustomerID	0	0	1
AdventureWorks2008	Sales	SalesOrderHeader	SalesPersonID	0	0	1
AdventureWorks2008	Sales	SalesOrderHeader	TerritoryID	0	0	1
AdventureWorks2008	Sales	SalesOrderHeader	BillToAddressID	0	0	1
AdventureWorks2008	Sales	SalesOrderHeader	ShipToAddressID	0	0	1
AdventureWorks2008	Sales	SalesOrderHeader	ShipMethodID	0	0	1
AdventureWorks2008	Sales	SalesOrderHeader	CreditCardID	0	0	1
AdventureWorks2008	Sales	SalesOrderHeader	CreditCardApprovalCode	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	CurrencyRateID	0	0	1
AdventureWorks2008	Sales	SalesOrderHeader	SubTotal	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	TaxAmt	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	Freight	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	TotalDue	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	Comment	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	rowguid	0	0	0
AdventureWorks2008	Sales	SalesOrderHeader	ModifiedDate	0	0	0