How to find tables and columns by name or partial name

book

Article ID: 179799

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server) Inventory Solution

Issue/Introduction

 

Resolution

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