Select * from information_schema,tables where table_name ='foo'

Select * from information_schema,columns where column_name ='foo'

Select * from table_name where col1 ='foo'

SET nocount ON

DECLARE @string VARCHAR(1000)

SET @string = ''

DECLARE
  @errnum INT,
  @errors CHAR(1),
  @rowcnt INT,
  @output VARCHAR(255)

SELECT
  @errnum = 0,
  @errors = 'N',
  @rowcnt = 0,
  @output = ''        

DECLARE @Results TABLE (
   Name VARCHAR(55),
   Type VARCHAR(12),
   DateCreated DATETIME,
   ProcLine VARCHAR(4000)
  )


INSERT
INTO @Results
  SELECT DISTINCT
    'Name' = CONVERT(VARCHAR(55), SO.name),
    'Type' = SO.type,
    crdate,
    ''
  FROM
    sysobjects SO
    JOIN syscomments SC
      ON SC.id = SO.id
  WHERE
    SC.text LIKE '%' + @string + '%'
  UNION
  SELECT DISTINCT
    'Name' = CONVERT(VARCHAR(55), SO.name),
    'Type' = SO.type,
    crdate,
    ''
  FROM
    sysobjects SO
  WHERE
    SO.name LIKE '%' + @string + '%'
  UNION
  SELECT DISTINCT
    'Name' = CONVERT(VARCHAR(55), SO.name),
    'Type' = SO.type,
    crdate,
    ''
  FROM
    sysobjects SO
    JOIN syscolumns SC
      ON SC.id = SO.ID
  WHERE
    SC.name LIKE '%' + @string + '%'
  ORDER BY
    2,
    1

SELECT
  Name,
  'Type' = CASE (Type)
             WHEN 'P' THEN 'Procedure'
             WHEN 'TR' THEN 'Trigger'
             WHEN 'X' THEN 'Xtended Proc'
             WHEN 'U' THEN 'Table'
             WHEN 'C' THEN 'Check Constraint'
             WHEN 'D' THEN 'Default'
             WHEN 'F' THEN 'Foreign Key'
             WHEN 'K' THEN 'Primary Key'
             WHEN 'V' THEN 'View'
             ELSE Type
           END,
  DateCreated
FROM
  @Results
ORDER BY
  2,
  1