Filter And Get List Of Tables And Stored Procedures Created In The Database Using SQL Query

Introduction

Using "sys.objects", we can get a list of tables and Stored Procedures created in the Database.

Description

You can apply the OBJECT_ID, OBJECT_NAME, and OBJECTPROPERTY() built-in functions to the objects shown in sys.objects. It contains a row for each user-defined, schema-scoped object that is created within a database. sys.objects does not show DDL triggers because they are not schema-scoped.

 
Steps

Run the below SQL query to get list of tables created in the Database.

SELECT * FROM sys.objects where type in (N'U')

 
Here, 'U' means user table.

 

You can filter for a particular table name instead of using right click in Object Explorer. 

SELECT * FROM sys.objects where type in (N'U') and name='tblSatya'

 

 
Run the below SQL query to get a list of Stored Procedures created in the Database.

SELECT * FROM sys.objects where type in (N'P')

  

Here, 'P' means Stored Procedure.

You can filter for particular Stored Procedure name instead of using right click in Object Explorer. 

SELECT * FROM sys.objects where type in (N'P') and name='Sp_Satya'

 

 

Summary

Here, we learned the followig. 

How to count list of Tables and Stored Procedures created in the Database.
What is sys.objects.
Types used in sys.objects.
Filter for Tables and Stored Procedures using sql query.