Find all or any Table & Schema In SQL Server database(Using System Procedures)

Introduction:  

In this we will check how we can find Stored Procedure and Views in sql server database using query. We will use system stored procedure to find the Stored Procedure and Views .

Find Stored Procedure(s)

1.         To find all the stored procedures with details

                SELECT * FROM sys.procedures

2.         To find specific/ particular stored procedure with details

SELECT * FROM sys.procedures WHERE name ='Delete_Dept'

3.         To find all the stored procedures matching the specified pattern

SELECT * FROM sys.procedures WHERE name LIKE '%Delete%'

4.        To find all the stored procedures by schema id

SELECT * FROM sys.procedures WHERE schema_id=1

Find View(s)

1.         To find all the views with full details

SELECT * FROM sys.views

2.         To find specific/ particular view with full details

SELECT * FROM sys.views WHERE name ='vwEmpDetails'

3.         To find all the views matching the specified pattern

SELECT * FROM sys.views WHERE name LIKE '%vwEmp%'

4.         To find all the views by schema id

SELECT * FROM sys.views WHERE schema_id=1