SQL query to get table names and schema in Database

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';

Common Table Expression (CTE) in Sql Server

Common Table Expressions, or CTEs, can be thought of as temporary views that are created in memory for a single execution scope. They allow you to simplify complex joins and subqueries by defining a named result set that exists only until the next query runs. Whether you are using Recursive or Non-Recursive CTEs, understanding their limitations—such as being unindexable and requiring immediate execution—is key to writing efficient SQL code.

• We can take CTE as tempory view (can be thought as tempory result set) that create in memory

• Its scope valid until next query run

• CTE need to use just after it declaration  

• There are two type Recursive and Nonrecursive CTE

• Can define multiple CTE

• CTE are unindexable ( it cau use exist index in referenced object)

• CTE can't have constrain

• ORDER BY clause can't be used in the CTE_Query_Definition , execpt when use TOP keyword

Simple Example.

ex 1

WITH CTE_CUSTOMER_EX2
(         [CustomerID]
          ,[CompanyName]
  ,[ContactName]
  ,[ContactTitle]
  ,[Address]
  ,[City]
)
AS
(
SELECT [CustomerID]
  ,[CompanyName]
  ,[ContactName]
  ,[ContactTitle]
  ,[Address]
  ,[City]

  FROM .[dbo].[Customers]
)


SELECT * FROM CTE_CUSTOMER_EX2

ex 2

;WITH CTE_CUSTOMER_EX1
AS
(
SELECT [CustomerID]
 ,[CompanyName]
 ,[ContactName]
 FROM .[dbo].[Customers]

)
SELECT * FROM CTE_CUSTOMER_EX1

Note
If there are query before CTE define , need to use Semicolon teminator before declare CTE


Multiple CTE

  With CTE_M1 as 
(
-select statement
) 
     , CTE_M2 as 
(
-select statemet

)

you can find some more detail and examples using this Link and Link2