How to find tables and columns by name or partial name

Article:HOWTO2717  |  Created: 2006-03-13  |  Updated: 2012-01-30  |  Article URL http://www.symantec.com/docs/HOWTO2717
Article Type
How To



Question
How do I find tables and columns by name?

Answer
 

The following SQL query will find all TABLES containing the value specified in the SET @TableName statement:

--   Find all tables by full or partial name
--   Leading and trailing '%' signs are wildcards
--   o.Type: U=table, V=View, SP=Stored Procedure

DECLARE @TableName varchar(255)
SET @TableName = '%snmpd%'

select o.name 'Table', o.type 'Type'
 from sysobjects o
 where o.name like @TableName
   and o.type in ('U','V')
 order by o.type, o.name

The following SQL query will find all COLUMNS containing the value specified in the SET @ColumnName statement:

-- Find all columns, and their table, with a column name like @ColumnName
--   Leading and trailing '%' signs are wildcards
--   o.Type: U=table, V=View, SP=Stored Procedure

DECLARE @ColumnName varchar(255)
SET @ColumnName = '%host%'

select o.name, c.name, o.type
   from syscolumns c, sysobjects o
   where c.id = o.id
     and c.name like @ColumnName
     and o.type in ('U')
   order by o.name, c.name


Legacy ID



20565


Article URL http://www.symantec.com/docs/HOWTO2717


Terms of use for this information are found in Legal Notices