Search a table or view by name

Observed sign or problem

We know a word in the name—for example, “Order”—but we don’t know if the object is a table, a view, or what schema it is in. Opening folder by folder works on a small basis; on a legacy basis it soon ceases to be a reliable search.

What we need to distinguish

A schema is the namespace that groups objects inside a database, such as dbo or ventas. A pattern is a search form that may include wildcard characters.

First we want to locate objects whose name matches a pattern. It is also a good idea to distinguish tables from views and preserve the schema, because dbo.Pedido and ventas.Pedido are different objects.

Query

DECLARE @Patron SYSNAME = N'%Pedido%';

SELECT
    s.name AS Esquema,
    o.name AS Objeto,
    o.type_desc AS Tipo,
    o.create_date AS FechaCreacion,
    o.modify_date AS FechaModificacion
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
    ON s.schema_id = o.schema_id
WHERE o.type IN ('U', 'V')
  AND o.is_ms_shipped = 0
  AND o.name LIKE @Patron
ORDER BY
    o.type_desc,
    s.name,
    o.name;

U represents a user-defined table and V represents a view. The is_ms_shipped = 0 filter prevents including objects marked as created by SQL Server internal components.

Parameters or permissions

Catalog views expose metadata: data that describes the structure of other objects.

Change @Patron. The signs % allow text to exist before or after: N'%Pedido%' finds Pedido, PedidoDetalle, and HistoricoPedidos. To search for the beginning of the name use N'Pedido%'; for an exact match, N'Pedido' and the operator =.

The query works on the active database. If there are several bases, you must execute it in each one or prepare a controlled search between bases. Catalog views only display metadata for objects that the account has visibility over.

How to read the result

Each row identifies a visible object. Always read Esquema and Objeto as a unit. Tipo confirms whether you are looking at a table or a view.

FechaModificacion serves as a hint, not a change history. In tables and views it can also change when an index is created or modified; by itself it does not prove that the logic or structure you are investigating has changed.

If you know the full name, you can confirm their identity without relying on a partial match:

SELECT OBJECT_ID(N'dbo.Pedido', N'U') AS IdTabla;

A value other than NULL confirms that a visible table with that two-part name exists in the current database.

What this does establish

Allows you to assert which visible tables and views in the current database match the pattern, along with their schema and type.

What this still does not establish

An empty result does not prove that the object does not exist on another base, under another name, or outside your permissions. It also does not include synonyms, external tables or objects whose relationship with “Order” only appears in a column or in its code.

Next reasonable check

When you find candidates, search their columns. If the name appears within procedures, views, or functions, locate the text within SQL objects. For registered relationships, check the dependencies and foreign keys.

Search SQL Server