• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Microsoft SQL Server / Get a list of tables with MS SQL Server

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.

Check Out These Related posts:

  1. Describe table structure with MS SQL Server
  2. Backing up MySQL with mysqldump
  3. MySQL utility commands
  4. List stored procedures with MS SQL Server

Filed Under: Microsoft SQL Server

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap