SQL statement: Displays information from all tables in SQL Server

Display information for all tables or views in a database in a SQL Server
The difference between SQL Server 2000 and 2005 is in the Red Word section
The following statement takes all table information and replaces the green bold word "U" as "V" for all View information.

SQL Server 2000 Version

SELECT Sysobjects.name as TABLE_NAME, syscolumns. Id, syscolumns.name as column_name,
Systypes.name as Data_type, syscolumns.length as Character_maximum_length,
Sysproperties. [Value] As Column_description, Syscomments.text as
Column_default,syscolumns.isnullable as is_nullable from syscolumns
INNER JOIN systypes
On syscolumns.xtype = Systypes.xtype
Left JOIN sysobjects on syscolumns.id = sysobjects.id
Left OUTER JOIN sysproperties on
(Sysproperties.smallid = Syscolumns.colid
and sysproperties.id = syscolumns.id)
Left OUTER JOIN syscomments on syscolumns.cdefault = syscomments.id
WHERE Syscolumns.id in
(SELECT ID from sysobjects WHERE xtype = ' U ') and (systypes.name <> ' sysname ')
ORDER BY Syscolumns.colid

SQL Server 2005 version

SELECT Sysobjects.name as table_name, syscolumns. Id, Syscolumns.name as column_name,
Systypes.name as Data_type, syscolumns.length as Character_maximum_length,
Sys.extended_properties. [Value] As column_description,  Syscomments.text as
Column_default,syscolumns.isnullable as IS_NULLABLE from syscolumns
INNER JOIN systypes
    on syscolumns.xtype = Systypes.xtype
    Left JOIN sysobjects on syscolumns.id = Sysobjects.id
   left OUTER JOIN sys.extended_properties on
&nbs p;  (sys.extended_properties.minor_id = Syscolumns.colid
     and Sys.extended_ properties.major_id = syscolumns.id)
   left OUTER JOIN syscomments on syscolumns.cdefault = syscomments.id
   where syscolumns.id in 
    (SELECT ID from sysobjects WHERE xtype = ' U ') and (s Ystypes.name <> ' sysname ')
    order by Syscolumns.colid

Reference: Http://www.devx.com/tips/Tip/31235?type=kbArticle&trk=MSCP