Wednesday 18 April 2012

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

 

 

2 comments: