Find all or any Table & Schema In SQL Server database(Using System Procedures)

Introduction:  

In this we will check how we can find tables and Schema in sql server database using query. We will use system stored procedure to find the tables and Schema.

Find Schema(s)

1.        Query to find all the schemas with details

                SELECT * FROM sys.schemas

2.       Query to find specific schema with details

               SELECT * FROM sys.schemas WHERE name = '%Dept_table%'

3.         To find all the schemas matching the specified pattern

              SELECT * FROM sys.schemas WHERE name LIKE '%Dept%'

4.         To find schema by schema id

             SELECT * FROM sys.schemas WHERE schema_id=1

Find Table(s)

1.         To find all the tables with full details

  SELECT * FROM sys.tables

2.         To find specific/ particular table with full details

SELECT * FROM sys.tables WHERE name = 'Dept_table'

3.         To find all the tables matching the specified pattern

SELECT * FROM sys.tables WHERE name LIKE '%Dept%'

4.         To find all the tables by schema id

SELECT * FROM sys.tables WHERE schema_id=1