Finding tables and columns by name or partial name

Products

• IT Management Suite
• Inventory Solution

Issue/Introduction

Question
How do I find tables and columns by name?

Resolution

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