To search all tables to find a column or field name in a database

SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE syscolumns.name='EMPNAME'

---------------------------

In this query it displays LIST OF TABLES ALONG WITH TABLE COUNT in a DATABASE

SELECT so.[name] as [table name],rowcnt as ROWS,

CASE WHEN si.indid between 1 and 254

THEN si.[name] ELSE NULL END AS [Index Name] ,

si.indid FROM sysindexes si INNER JOIN sysobjects so ON

si.id = so.id WHERE si.indid < type =" 'U'">

AND so.[name] != 'dtproperties'

ORDER BY so.[name]