Query to Find All About Database Schema

When we design a database, there are tables, Stored Procedures, Views and functions. Each table has some columns and each column has its default values. Sometimes we need to determine how many tables this specific column is in. Sometimes you need to check the default values of your columns. When we design a database for large systems, it may contain more than 100 tables, and if a person wants to check or want to change anything on all the tables, for example a person wants to check, in my database, how many tables have a Date Of Joining (DOJ) field. And they want to check in all tables whether or not the default value is getdate(). How will they determine that? Yes they can go to the object explorer and select the database and check all the tables. But it would be terrible, if in a database there are more than 100 tables. Today I will show you how you can check all the Schema.
In SQL Server, there is an Information_Schema. With the use of information_schema, you can get all your schema details.

1. Information_Schema.Columns: Using it, you can get all the information about all the tables and columns of your database.
   
   Step 1: Just fire a query SELECT * FROM INFORMATION_SCHEMA.COLUMNS then press F5.
   
   
   
   In the preceding screen, we have a School database. And in this database, when I press F5, I’m getting all the table information.
   
   The details of some important columns of Information_Schema.Columns are:
   
   
   1. Table_Catalog: Here you will find the name of database.
   2. Table_Schema: Here you will find your table schema.
   3. Table_Name: As you now the name of the table.
   4. Column_Name: Name of column by table.
   5. Ordinal_Position: This is the thing for defining your columns in each table. Like order by, in which no your column comes.
   6. Column_Default: Default value of specific column by table.
   7. Is_Nullable: Field is nullable or not.
   8. Data_Type: Check the datatype of a field by table.
   9. Cherecter_Maximum_Length: Max length size of a column.
   10. Cherecter_Octet_Length: Maximum length, in bytes, for binary data, character data, or text and image data. -1 for XML and large-value type data. Otherwise, NULL is returned.
       
2. Information_Schema.Tables: This is all about your database tables and views. Here you will get the information about tables.
   
   Step 1: SELECT * FROM INFORMATION_SCHEMA.TABLES Press F5.
   
   
   
   You can see in the screen above, here we have the details of the table name with its type. I know in daily use this is not useful, but you should know about it.
   
3. Information_Schema.Table_Constraints: In every table there is a primary key and foreign key; you will get all the info about the table constraints.
   
   SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS