Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_tables_ex

 

Returns table information about the tables from the specified linked server.
Execution syntax:
sp_tables_ex [ @table_server = ] 'table_server'
    
[ , [ @table_name = ] 'table_name' ]
    [ , [ @table_schema = ] 'table_schema' ]
    [ , [ @table_catalog = ] 'table_catalog' ]
    
[ , [ @table_type = ] 'table_type' ]

Arguments


[@table_server =] 'table_server'
Is the name of the linked server for which to return table information. table_server is sysname, with no default.
[,[@table_name =] 'table_name']
Is the name of the table for which to return data type information. table_name is sysname, with a default of NULL.
[@table_schema =] 'table_schema']
Is the table schema. table_schema is sysname, with a default of NULL.
[@table_catalog =] 'table_catalog'

 

Is the name of the database in which the specified table_name resides. table_catalog is sysname, with a default of NULL.

[@table_type =] 'table_type'

Is the type of the table to return. table_type is sysname, with a default of NULL, andcan have one of these values.

Value Description
ALIAS Name of an alias.
GLOBAL TEMPORARY Name of a temporary table available system wide.
LOCAL TEMPORARY Name of a temporary table available only to the current job.
SYNONYM Name of a synonym.
SYSTEM TABLE Name of a system table.
TABLE Name of a user table.
VIEW Name of a view.

 

Return Code Values

None

Result Sets

Column name Data type Description
TABLE_CAT sysname Table qualifier name. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment. This field can be NULL.
TABLE_SCHEM sysname Table owner name. In SQL Server, this column represents the name of the database user who created the table. This field always returns a value.
TABLE_NAME sysname Table name. This field always returns a value.
TABLE_TYPE varchar(32) Table, system table, or view.
REMARKS varchar(254) SQL Server does not return a value for this column.

 

Remarks

sp_tables_ex is executed by querying the TABLES rowset of the IDBSchemaRowset interface of the OLE DB provider corresponding to table_server. The table_name, table_schema, table_catalog, and column parameters are passed to this interface to restrict the rows returned.

sp_tables_ex returns an empty result set if the OLE DB provider of the specified linked server does not support the TABLES rowset of the IDBSchemaRowset interface.

Permissions

Execute permission default to the public role.

Examples

This example returns table information about the titles table in the pubs database, on the LONDON2 linkedserver.

USE master EXEC sp_tables_ex 'LONDON2', 'titles', 'dbo', 'pubs', NULL This is a procedure in the mssqlsystemresource database.


Syntax

create procedure sys.sp_tables_ex
(
    @table_server       sysname,
    @table_name         sysname = null,
    @table_schema       sysname = null,
    @table_catalog      sysname = null,
    @table_type         sysname = null,
    @fUsePattern        bit = 1 -- To allow users to explicitly disable all pattern matching.
)
as
    if (@fUsePattern = 1) -- Does the user want it?
    begin
        if ((isnull(charindex('%', @table_name),0) = 0) and
            (isnull(charindex('[', @table_name),0) = 0) and
            (isnull(charindex('_', @table_name),0) = 0) and
            (isnull(charindex('%', @table_schema),0) = 0) and
            (isnull(charindex('[', @table_schema),0) = 0) and
            (isnull(charindex('_', @table_schema),0) = 0) and
            (isnull(charindex('%', @table_catalog),0) = 0) and
            (isnull(charindex('[', @table_catalog),0) = 0) and
            (isnull(charindex('_', @table_catalog),0) = 0))
        begin
            select @fUsePattern = 0 -- not a single wild char, so go the fast way.
        end
    end

    if @fUsePattern = 0
    begin
        /* -- Debug output, do not remove it.
        print '*************'
        print 'No pattern matching.'
        print @fUsePattern
        print isnull(@table_server, '@table_server = null')
        print isnull(@table_name, '@table_name = null')
        print isnull(@table_schema, '@table_schema = null')
        print isnull(@table_catalog, '@table_catalog = null')
        print isnull(@table_type, '@table_type = null')
        print '*************'
        */
        select
            TABLE_CAT   = rt.TABLE_CATALOG,
            TABLE_SCHEM = rt.TABLE_SCHEMA,
            TABLE_NAME  = rt.TABLE_NAME,
            TABLE_TYPE  = rt.TABLE_TYPE,
            REMARKS     = convert(nvarchar(255),rt.DESCRIPTION)
        from
            -- We can not pass @table_type directly here, because in ODBC we enclose
            -- it in '', which is not acceptable for OLEDB SProc.
            sys.fn_remote_tables(@table_server,
                                 @table_catalog,
                                 @table_schema,
                                 @table_name,
                                 NULL) rt
        where
            (charindex ('' + TABLE_TYPE + '', @table_type) <> 0 or @table_type is NULL)
        order by 4, 1, 2, 3
    end
    else
    begin
        /* -- Debug output, do not remove it.
        print '*************'
        print 'THERE IS pattern matching!'
        print @fUsePattern
        print isnull(@table_server, '@table_server = null')
        print isnull(@table_name, '@table_name = null')
        print isnull(@table_schema, '@table_schema = null')
        print isnull(@table_catalog, '@table_catalog = null')
        print isnull(@table_type, '@table_type = null')
        print '*************'
        */
        select
            TABLE_CAT   = TABLE_CATALOG,
            TABLE_SCHEM = TABLE_SCHEMA,
            TABLE_NAME  = TABLE_NAME,
            TABLE_TYPE  = TABLE_TYPE,
            REMARKS     = convert(nvarchar(255),DESCRIPTION)
        from
            sys.fn_remote_tables(@table_server,
                                 @table_catalog,
                                 NULL,
                                 NULL,
                                 NULL)
        where
            (TABLE_SCHEMA like @table_schema or
             @table_schema is NULL or
             (TABLE_SCHEMA is NULL and @table_schema = N'%')) and
            (TABLE_CATALOG like @table_catalog or
             @table_catalog is NULL or
             (TABLE_CATALOG is NULL and @table_catalog = N'%')) and
            (charindex ('' + TABLE_TYPE + '', @table_type) <> 0 or @table_type is NULL) and
            (TABLE_NAME like @table_name or
             @table_name is NULL)
        order by 4, 1, 2, 3
    end

 
Last revision 2008RTM
See also

  sp_ddopen (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash