The query below finds tables with a specific name in all the schemas in a database. In this case, it searches for the 'product' table.
select schema_name(t.schema_id) as schema_name, t.name as table_name from sys.tables as t where t.name = 'product' order by schema_name, table_name;
- schema_name - name of the schema where the table was found
- table_name - name of the table (redundant as it should be exactly the same as provided)
- One row: represents a table
- Scope of rows: all found tables
- Ordered by: schema name
- There might be more tables than one because the different schemas in a database may have tables with the same names