how-to get sql server 2008 tables' and columns' meta data

-- get columns meta data 

SELECT top 100000

 INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG              AS 'DataBaseName' 

,INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA               AS 'SchemaName'

,SysObjects.NAME                                       AS 'TableName'

,sys.all_columns.NAME                                  AS 'ColumnName'

,INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION           AS 'OrdinalPosition'

,sys.types.name                                        AS 'DataType' 

,sys.all_columns.is_nullable                           AS 'IsNullable' 

,sys.all_columns.is_identity                           AS 'IsPrimaryKey'  

,sys.all_columns.max_length                            AS 'MaxLength' 

,sys.all_columns.is_computed                           AS 'IsComputed' 

,syscomments.text                                      AS 'DefaultValue'

FROM dbo.sysobjects  

INNER JOIN sys.all_columns 

   ON ( SysObjects.id = sys.all_columns.object_id )

INNER JOIN sys.types 

   on ( sys.types.system_type_id = sys.all_columns.system_type_id )

LEFT JOIN syscomments  

   on ( SysObjects.id = syscomments.id ) 

-- LEFT JOIN sys.tables stb on sc.object_id = SysObjects.parent_obj

LEFT JOIN INFORMATION_SCHEMA.COLUMNS  

on  (     INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=SysObjects.NAME 

      and sys.all_columns.NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME ) 

--LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id  

--inner join sys.types st

--on sc.xtype = st.system_type_id

WHERE 1=1 

AND SysObjects.xtype = 'U'  

and sys.types.name <> 'sysname' --to a bug with this one occurs if enabled 

and sys.all_columns.is_computed = 0 --todo a bug with this one occurs if enabled 

-- and SysObjects.name = @TableName --uncomment this line while debugging

-- AND sys.all_columns.name LIKE '%TABLE_NAME_TO_SRCH%'

ORDER BY DatabaseName , SchemaName , TableName , INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION

;