How to attach [dbo] prefix to table name when selecting all tables from a given database?

Question

I'm performing the query which is listed below the question using SSMS or directly C# code. As a result, I get a table with a list of names all available tables in this database.

In majority of cases these table names can be used directly in all kind of 'SELECT' queries. However, if the current database is AdventureWorks2017, then all table names are corrupted. In case if I want to perform any 'SELECT' query, I have to manually add all prefixes which are not [dbo] (like, for example, [HumanResources], [Person], etc).

Also, my question is: what kind of query should I use in order to get the full prefixed table name (like [AdventureWorks2017].[HumanResources].[Employee] or [Northwind].[dbo].[CustomerCustomerDemo])?

use 'someDBName' 
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'someDBName'

There's an add-in for SSMS called ApexSQL which can format your queries like that — BytesOfMetal, Jan 29 '19 at 10:18
Please read about schemas. https://stackoverflow.com/questions/529142/what-good-are-sql-server-schemas — EzLo, Jan 29 '19 at 11:39

noonand · Accepted Answer · 2019-01-29T11:20:22.740

SELECT '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS SchemaAndTable
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'DbName'

FWIW, the table names are not corrupted, they're just in a different schema than the usual (default) one of dbo

To get the database name as well you'd do this:

SELECT '[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS DbSchemaAndTable
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'DbName'

How to attach [dbo] prefix to table name when selecting all tables from a given database?

1 Answers1