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.

 In previous article i explained Sql server query to get second,third,fourth or nth highest salary and 20 main differences between Stored procedures and Functions in Sql Server and Difference between Delete and Truncate in sql server and How to Create table in sql server having auto increment primary key column 

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." 

• Tweet
• Share
• Share
• Share

Related Post