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