SQL Server System Views

In this article we will learn; How to find Database objects i.e. Tables, Indexes, Procedures, Triggers and Views using catalog views. As we all know once we create objects it gets stored in our database, now let assume your database has many tables, Views, Triggers and you are not aware of the exact count of each object; Now you want to know, how many Tables, Views  and other objects presnet in your database. It's not a difficult task, you can easily find all information from SQL Server System Catalog Views. We have many system Views inside our SQL Server database. You can find all system views under Views folder in your database. Open Object Explorer  go in your database and then go in Views folder under view folder you will find system Views folder, there you go all you system views are present in this folder  System views are also known as Catalog Views.You can right click and Select Top 1000 rows to see the data. 

You can write a Simple SQL Query against these system views if you want to see list of all tables inside your database. 

SELECT * FROM SYS.TABLES

Below is the list all important sys views and their purpose.

Lets go through with one example, we will create a fresh new database and we will create two tables and then we will find how many tables are present in your database.

Create Database Teachmesqlserver 

We have created one database in sql server, if you wants to search how many databases are there in your sql server you can easily find it from your system views

If you want to search how many databases are there in your SQL Server run below query

Select * from Sys.Databases 

 

You will find just now created Teachmesqlserver database along with other databases if present in your sql server.

Let’s create two new tables in our recently created database

Create Table EMP(EMPID int)

Go

Create Table DEP(DEPID Int)

Now let’s find how many tables our database has, we will run simple SQL Query against Sys.Tables system view.

SELECT * FROM SYS.TABLES

Below is the list of all important system views.

If you liked the article hit the Facebook like button.

Keep Learning!