Get a list of tables with MS SQL ServerGet a list of tables with MS SQL Server

Posted July 16th, 2008 in Microsoft 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.

Recent posts: