Get 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:
- List installed packages with YUM (Tuesday, December 2nd 2008)
- Monthly Roundup - November 2008 (Monday, December 1st 2008)
- Weekly Roundup - December 1st 2008 (Monday, December 1st 2008)
- Installing subversion on CentOS (Sunday, November 30th 2008)
- GoDaddy 99 cent .com domain coupon code (Saturday, November 29th 2008)
- Find the index of a string within a string with Javascript (Friday, November 28th 2008)
Subscribe to RSS Feed / Email / Bookmark / Share
Use the buttons below to subscribe to my RSS feed to be notified next time something is posted, share this post with others, or subscribe by email and have my posts sent in a daily email.
