Monday, August 12, 2013

sp_tables

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.