sp_tables

It returns a list of objects that can appears in the FORM cluse. It unable to returns the synonym objects. To view the details related to synonym just go to the sys.synonyms system catalog views.

The syntax of the sp_tables is mentioned bellow.

sp_tables [ [ @table_name= ] 'name' ]

     [ , [ @table_owner= ] 'owner' ]

     [ , [ @table_qualifier= ] 'qualifier' ]

     [ , [ @table_type= ] "type" ]

     [ , [@fUsePattern = ] 'fUsePattern'];

Now we are discussing about each of the parameters

1.    [ @table_name = ]
Table name is used to return the catalog information. Supported nvarchar(384). Default value is NULL. Wildcard character can be used.

2.    [ @table_owner = ]
Owner of the table is used to return the catalog information. Supported nvarchar(384). Default is NULL.

3.    [ @table_qualifier = ]
It is the name of the table qualifier. In others RDBMS supports qualifier.owner.name. In SQL server it is the database name.

4.    [ @table_type] = ]
This can be includes TABLE, SYSTEMTABLE or VIEWS. The Default is NULL. It is the list of values separated by a comma, which gives the information about.

5.     [ @fUserPattern = ]
Determines whether the underscore ( _ ), percent ( % ), and bracket ( [ or ] ) characters are interpreted as wildcard characters. Valid values are 0 (pattern matching is off) and 1 (pattern matching is on). fUsePattern is bit, with a default of 1.

Example:

USE my_DB;

GO

EXEC sp_tables

     @table_name = '%',

     @table_owner = 'student',

     @table_qualifier = 'my_DB';

GO

Return Result set:

Columns	Data Type	Descriptions
TABLE_QUALIFIER	sysname	In SQL Server it contains the Database name.
TABLE_WONER	sysname	Name of the Database owner who create the table.
TABLE_NAME	sysname	The name of the Table.
TABLE_TYPE	Varchar(32)	Table, System Table or Views
REMARKS	Varchar(254)	No Value returned

Hope you like it.

Posted by: MR. JOYDEEP DAS