SQL statement: displays information in all tables in SQL Server.

Displays information about all tables or views in a database of an SQL Server.
The difference between SQL Server 2000 and 2005 lies in the red part.
The following statement is used to obtain information about all tables. Replace "U" with "v" with "U" to obtain information about all views.

SQL Server 2000

Select sysobjects. Name as table_name, syscolumns. ID, syscolumns. Name as column_name,
Policypes. Name as data_type, syscolumns. length as character_maximum_length,
Sysproperties . [Value] As column_description, syscomments. Text
Column_default, syscolumns. isnullable as is_nullable from syscolumns
Inner join policypes
On syscolumns. xtype = policypes. 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 (policypes. Name <> 'sysname ')
Order by syscolumns. colid

SQL Server 2005

Select sysobjects. Name as table_name, syscolumns. ID, syscolumns. Name as column_name,
Policypes. Name as data_type, syscolumns. length as character_maximum_length,
SYS. extended_properties . [Value] As column_description, syscomments. Text
Column_default, syscolumns. isnullable as is_nullable from syscolumns
Inner join policypes
On syscolumns. xtype = policypes. xtype
Left join sysobjects on syscolumns. ID = sysobjects. ID
Left Outer Join SYS. extended_properties On
( 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 (policypes. Name <> 'sysname ')
Order by syscolumns. colid

Refer:Http://www.devx.com/tips/Tip/31235? Type = kbarticle & TRK = mscp