Get a list of tables with MS SQL Server

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.