Get list of Tables & Views in MS SQL Server- sp_tables

Introduction

Sometimes we may need to get all the tables and views from a database in MS SQL Server. By using T-SQL Statement sp_tables, we can retrieve a list of tables and views.

Syntax
sp_tables [ @table_name = 'Table name.' ] ,
[ @table_owner = 'The database user who created the table.' ] ,
[ @table_qualifier = 'Database name.' ] ,
[ @table_type = "Table, system table, or view." ] ;

The arguments are optional one. Let have a look at the below example,

Example
EXEC sp_tables
Fig.1 List of objects in the current environment

When we run the above T-SQL Statement sp_tables, in the current environment we will get a list of objects. The result columns are, TABLE_QUALIFIER

  • TABLE_QUALIFIER – This column returns the Database Name.
  • TABLE_OWNER – This column returns the Owner of objects, i.e. SCHEMA NAME.
  • TABLE_NAME – Returns Table Name / View Name.
  • TABLE_TYPE – Returns the type, either its table or view.
  • REMARKS– Return if any remarks, but SQL Server does not return any value

Returning Object based on Specific Schema

When sp_tables is executed, it will return all the object, but in some cases we need objects from a specified schema, that also we can achieve by passing the arguments with values.

Example
EXEC sp_tables   
@table_name = '%',  
@table_owner = 'Details',  
@table_qualifier = 'PracticalWorks';

From the above T-SQL, we need all the objects of the Schema Owner “Details” from the database “PracticalWorks”.

Arguments
  • @table_name – Table or View name which is to be returned
  • @table_owner – SCHEMA OWNER Name which is to be returned
  • @table_qualifier – Pass the Database name, from we need to get the list of objects
  • @table_type– Table or view which is to be returned
Fig.2 Specified List of Objects

Conclusion

By using sp_tables T-SQL Statement, we can get list of objects (Table and View) in the current environment. I hope this tip was very helpful. Please share your feedbacks in the comments sections.

Read other articles of mine,

One thought on “Get list of Tables & Views in MS SQL Server- sp_tables

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: