There are several ways to retrieve table names and their associated schemas within a SQL Server database. Depending on your specific needs—whether you are automating a script or performing a quick manual check—different methods offer different levels of detail. Below, I have outlined some of the most common and effective ways to query your database metadata.
1.SELECT SCHEMA_NAME(schema_id) 'SchemaName' , name 'TableInDb'
FROM sys.tables
Return all tables and related schema in selected database
2.
SELECT TABLE_CATALOG ,TABLE_SCHEMA ,TABLE_NAME ,TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
this will return more information than first one, it's return VIEWs in Database as well
3.
SELECT t.name 'TableName' ,s.name 'SchemaName'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
SELECT t.name 'TableName' ,s.name 'SchemaName'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = N'dbo';
