I’ve recently been doing some work with Microsoft SQL Server but the server itself for this particular customer is behind a firewall and I have no way currently of connecting with the SQL Server tools, so have to access it via some PHP scripts running on a webserver inside the network. This is the first in a series of three posts about using the sp_tables, sp_columns and sp_stored_procedures stored proecedures about SQL Server databases. This first post looks at sp_tables.
sp_tables returns a list of tables and views that can be queried in a selected database. The basic usage is as follows:
sp_tables [ [ @table_name = ] 'name' ]
[ , [ @table_owner = ] 'owner' ]
[ , [ @table_qualifier = ] 'qualifier' ]
[ , [ @table_type = ] "type" ]
[ , [@fUsePattern = ] 'fUsePattern'];
On its own, calling “exec sp_tables” will simply list all the tables and views for the current database, as shown in the following example resultset:
| TABLE_QUALIFIER | TABLE_OWNER | TABLE_NAME | TABLE_TYPE | REMARKS |
| MyDBName | dbo | ExampleTable1 | TABLE | |
| MyDBName | dbo | ExampleTable2 | TABLE |
You can use the @table_name @table_owner and @table_qualifier parameters with wildcards to return a smaller resultset. @table_name queries the table names, @table_owner the owners and @table_qualifier the database name. For example, to only return tables which start with the letter “a” you could execute either of these:
EXEC sp_tables "a%"; EXEC sp_tables @table_name = "a%";
The @table_type parameter lets you specify whether tables, system tables and/or views should be returned. If not specified then it will return all of them. To use this parameter, the values are a single quote and comma separated list inside double quotes, as shown in the following example:
EXEC sp_tables @table_type = "'view'"; EXEC sp_tables @table_type = "'table', 'view'";
The @fUsePattern allows you to specify whether _ % and [ ] characters are interpreted as wildcard characters or not. If set to 0 then pattern matching is switched off and those characters must be present in the table name etc to be returned. If set to 1 (or not specified – the default is on) then wildcard pattern matching is switched off.
So that’s a basic overview of the sp_tables stored procedure for listing tables and views in Microsoft SQL Server. The next post in this series (in a week’s time) will look at sp_columns to get a list and description of the columns in a SQL Server table.
