SP_TABLES is a stored procedure in SQL Server that allows you to list tables and views in a database. You can use it to retrieve information about all tables in your current database. Here's how you can use it:
EXEC sp_tables @table_type = 'TABLE';
Explanation of the Parameters:
- @table_type: Specifies the type of objects to return. Common values are:
- 'TABLE' for user tables.
- 'VIEW' for views.
- You can also specify TABLE, VIEW, PROCEDURE, etc., as needed.
Listing All Tables in a Specific Database:
If you want to list tables from a specific database, you first need to switch to that database using the USE statement, like this:
USE YourDatabaseName; -- Replace with your database name
EXEC sp_tables @table_type = 'TABLE';
Example:
Here’s a complete example assuming you are working with a database named SampleDB:
USE SampleDB;
EXEC sp_tables @table_type = 'TABLE';
This will give you a result set containing the list of all user tables in the SampleDB database.
Additional Options:
If you want to filter the results further (for example, to list tables based on the schema), you can add additional parameters:
EXEC sp_tables @table_name = '%', @table_type = 'TABLE', @table_owner = 'dbo';
In the above command, replace 'dbo' with the desired schema name to get tables belonging to that schema.
This method of using SP_TABLES provides a straightforward way to query the list of tables available in a SQL Server database.