SQL query to get table names and schema in Database

SQL query to get Table and Schmas in Database

There are many way to get table names and related schema in Database, Below I mention some of them

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