Find all or any Sql Server Table,View,Stored Procedure,User Defined Function and Schema in a database

Introduction:  In this short article I am going to share sql server commands/queries  to get or check all or specific schemas, tables, stored procedures(SP), views and user defined functions (UDF) created under a database.


Description:  I have mentioned multiple statements/commands through which you can check all or any particular schemas, tables, stored procedures, views and user defined functions created under a database.

 Implementation: Let’s find by executing the below mentioned commands:

Find Schema(s)

Purpose:  To find all the schemas with full details
Query :   SELECT * FROM sys.schemas

Purpose:  To find specific/ particular schema with full details
Query SELECT * FROM sys.schemas WHERE name = '%EMPLOYEE%'

Purpose:  To find all the schemas matching the specified pattern
Query :  SELECT * FROM sys.schemas WHERE name LIKE '%EMP%'

Purpose:  To find schema by schema id
Query :  SELECT * FROM sys.schemas WHERE schema_id=1


Find Table(s)

Purpose:  To find all the tables with full details
Query :  SELECT * FROM sys.tables

Purpose:  To find specific/ particular table with full details
Query :  SELECT * FROM sys.tables WHERE name = 'tbEmployeeDetails'

Purpose:  To find all the tables matching the specified pattern
Query :  SELECT * FROM sys.tables WHERE name LIKE '%tbEmp%'

Purpose:  To find all the tables by schema id
Query :  SELECT * FROM sys.tables WHERE schema_id=1 


Find Stored Procedure(s)

Purpose:  To find all the stored procedures with full details
Query :  SELECT * FROM sys.procedures

Purpose:  To find specific/ particular stored procedure with full details
Query :  SELECT * FROM sys.procedures WHERE name ='spSaveEmployeeDetails'

Purpose:  To find all the stored procedures matching the specified pattern
Query :  SELECT * FROM sys.procedures WHERE name LIKE '%spEmp%'

Purpose: To find all the stored procedures by schema id
Query :  SELECT * FROM sys.procedures WHERE schema_id=1


Find View(s)

Purpose:  To find all the views with full details
Query :  SELECT * FROM sys.views

Purpose:  To find specific/ particular view with full details
Query :  SELECT * FROM sys.views WHERE name ='vwEmployeeDetails'

Purpose:  To find all the views matching the specified pattern
Query :  SELECT * FROM sys.views WHERE name LIKE '%vwEmp%'

Purpose:  To find all the views by schema id
Query :   SELECT * FROM sys.views WHERE schema_id=1


Find User Defined Functions

Purpose:  To find all the User Defined Functions(UDF’s) 
Check the link  :Multiple sql server commands to find all User Defined Functions(UDF’s)

Now over to you:
" I hope you have got the way to find table, view, stored procedure, user define function and schema in sql  server and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..