How to check the schema of your Database through queries

One nice thing is to be able to check and to query the schema of your database through writing simple queries and not through code.

For example, if you want to change a table structure or delete it, you want to find all the SPs and Views that use this table. Another example, if you want to check for schema changes between 2 databases, you can do that using simple queries. Another example, if you want to get the script of stored procedure of view using queries (for example writing a T-SQL to get you the script of some stored procedures)

 We are going to talk in this blog on 4:

1. INFORMATION_SCHEMA.TABLES: This is a system view that allows to see all the tables in your database

2. INFORMATION_SCHEMA.COLUMNS: This is also a system view that allows to see all the columns along with their metadata in your database

3. INFORMATION_SCHEMA.ROUTINES: This is also a system view that will get you all the stored procedures in your database

4. sys.sql_modules: This is a system table that stores the script of the stored procedures in your database.

         Example: SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.InsertStudent');

         This will get you the script of the stored procedure "InsertStudent"