SQL Server : Query to get list of Columns for each table

Below is  the query to get the list of the all the columns with specific detail like datatype, maximum length, IsUserDefined for all the user created tables. This script works for SQL Server 2005, SQl Server 2008, SQL Server 2008 R2 and SQL Server 2012 ( Release Candidate).



SELECT
OBJECT_NAME(COL.OBJECT_ID) TableName,
COL.Name AS ColumnName,
SCHEMA_NAME(TP.schema_id) AS SchemaName,
TP.name AS TypeName,
COL.max_length,
TP.is_user_defined
FROM  SYS.Tables TBL
JOIN SYS.Columns AS COL ON TBL.object_id = COL.object_id
JOIN SYS.Types AS TP ON COL.user_type_id = TP.user_type_id
WHERE TBL.TYPE = 'U'
ORDER BY COL.OBJECT_ID