In this article we will learn where SQL server stores our object i.e. Tables, Indexes, Procedures, Triggers, Views etc. and how to find them using catalog views.
As we all know once we create objects it gets stored in our database. Suppose your database has many tables, Views, Triggers and want you find how many Tables, Views and other objects are there in your database don’t worry 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 all tables.
SELECT * FROM SYS.TABLES
Below I 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.
As we all know once we create objects it gets stored in our database. Suppose your database has many tables, Views, Triggers and want you find how many Tables, Views and other objects are there in your database don’t worry 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 all tables.
SELECT * FROM SYS.TABLES
Below I 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.
