find a column in SQL Server

There are coupe of queries in my mail box about How to find out a particular Column and its details from all the columns in SQL Server.

This is one of finding the information.

select sysobjects.name as 'TableName', *
from syscolumns, sysobjects
where syscolumns.name='columnname'
and sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')