Article ID: 203638
Article Last Modified on 2/2/2005
SRV_NAME Name of the linked server.
SRV_PROVIDERNAME Friendly name of the OLE DB provider managing
access to the specified linked server.
SRV_PRODUCT Product name of the linked server.
SRV_DATASOURCE OLE DB datasource property corresponding to the
specified linked server.
SRV_PROVIDERSTRING OLE DB provider string property corresponding to
the specified linked server.
SRV_LOCATION OLE DB location property corresponding to the
specified linked server.
SRV_CAT OLE DB catalog property corresponding to the
specified linked server.
sp_tables_ex returns information on the tables from the specified linked server. This stored procedure accepts the following arguments:
TABLE_SERVER Name of the linked server for which to return table
information, with no default.
TABLE_NAME Name of the table for which to return data type
information, with a default of NULL.
TABLE_SCHEMA Is the table schema, with a default of NULL.
TABLE_CATALOG Catalog Name of the database in which the specified
table resides, with a default of NULL.
TABLE_TYPE Type of the table to return, with a default of NULL.
TABLE_CAT Table qualifier name. (May be null) TABLE_SCHEM Table or view owner name. TABLE_NAME Table name. TABLE_TYPE Table, system table, or view. REMARKS SQL Server does not return a value for this column.
TABLE_SERVER Name of the linked server for which to return column
information, with no default.
TABLE_NAME Name of the table for which to return column
information, with a default of NULL.
TABLE_SCHEMA Name of the table for which to return column
information, with a default of NULL.
TABLE_CATALOG Catalog name of the table for which to return column
information, with a default of NULL.
COLUMN Name of the database column for which to provide
information, with a default of NULL.
ODBCVer The version of ODBC being used. ODBCVer is an
integer, with a default value of 2, indicating ODBC
Version 2. Valid values are 2 or 3.
TABLE_CAT Table or view qualifier name. (May be null)
TABLE_SCHEM Table or view owner name.
TABLE_NAME Table or view name.
COLUMN_NAME Column name, for each column of the specified table.
DATA_TYPE Integer value corresponding to ODBC type indicators.
TYPE_NAME String representing a data type.
COLUMN_SIZE Number of significant digits.
BUFFER_LENGTH Transfer size of the data.
DECIMAL_DIGITS Number of digits to the right of the decimal point.
NUM_PREC_RADIX Is the base for numeric data types.
NULLABLE Specifies nullability.
REMARKS This field always returns NULL.
COLUMN_DEF Default value of the column.
SQL_DATA_TYPE Value of the SQL data type as it appears in the TYPE
field of the descriptor.
SQL_DATETIME_SUB Subtype code for datetime and SQL-92 interval data
types. For other data types, this column returns
NULL.
CHAR_OCTET_LENGTH Maximum length in bytes of a character or integer
data type column. For all other data types, this
column returns NULL.
ORDINAL_POSITION Ordinal position of the column in the table.
IS_NULLABLE Nullability of the column in the table. This column
returns a zero-length string if nullability is
unknown. The value returned for this column is
different from the value returned for the NULLABLE
column.
SS_DATA_TYPE SQL Server data type, used by Open Data Services
extended stored procedures.
* NOTE: This code is based on the following assumptions:
* A DSN named LINKSRV pointing to a SQL Server data source exists.
* A Linked Server has been defined in SQL Server.
Connect_String="'LINKSRV','SA',''"
* Connect to SQL Server
gnConnHandle=SQLCONNECT(&Connect_String)
IF gnConnHandle>0
* Create a command string to pass to SQL Server via SQLExec.
CREATE CURSOR SQLSRVDAT (Server c(128),Table_Name c(128), ;
Column_name c(128))
* Create a command string to pass to SQL Server via SQLExec.
SQLCommand="sp_linkedservers"
* Return a cursor with the names of available linked servers.
QRYVal=SQLExec(gnConnHandle,SQLCommand,'SQLSvr')
IF QRYVal>0
SELECT SQLSvr
DO WHILE !EOF()
* Build a command to pass to SQL Server to return a cursor
* of tables available from the linked server.
SQLCommand="sp_tables_ex '"+ALLTRIM(SQLSvr.SRV_NAME)+"'"
* Return a cursor with the names of tables available from
* the specified linked server.
tables_val=SQLExec(gnConnHandle,SQLCommand,'SQLTabs')
IF tables_val>0
SELECT sqltabs
LOCA
DO WHILE !EOF()
* Build a command to pass to SQL Server to return a
* cursor with the fields from the specified
* table of the defined linked server.
SQLCommand="sp_columns_ex '"+ALLTRIM(SQLSvr.SRV_NAME) + ;
"','"+ALLTRIM(sqltabs.Table_Name)+"'"
* Return a cursor with the names of the columns
* in the specified table of the linked server.
ColVal=SQLExec(gnConnHandle,SQLCommand,'sqlcols')
IF ColVal>0
SELECT sqlcols
DO WHILE !EOF()
INSERT INTO SQLSRVDAT VALUES ;
(SQLSvr.SRV_NAME,sqltabs.Table_Name, ;
sqlcols.Column_name)
SELECT sqlcols
SKIP
ENDDO
ENDIF
SELECT sqltabs
SKIP
ENDDO
ENDIF
SELECT SQLSvr
SKIP
ENDDO
ELSE
=AERROR(L_Server)
=MESSAGEBOX(L_Server[1,2]+CHR(13)+L_Server[1,3],32,'Query Failed')
ENDIF
=SQLDISCONN(gnConnHandle)
SELECT SQLSRVDAT
IF RECCOUNT()=0
=MESSAGEBOX('No Linked Servers Detected',64,'Linked Servers')
ELSE
CALCULATE MAX(LEN(ALLTRIM(SQLSRVDAT.column_Name))) TO max_wide
BROW TITLE "Linked Server Information" WIDTH max_wide
ENDIF
ELSE
=AERROR(s_failed)
* The linked server doesn't exist, so display a message.
=MESSAGEBOX(s_failed[1,2]+CHR(13)+ ;
IIF(!ISNULL(s_failed[1,3]),s_failed[1,3],"null"),32,'Failed')
ENDIF
RETURN
* End Code.
DO SP_TABLES
Additional query words: kbDSupport kbDSE
Keywords: kbhowto kbsqlprog kbdatabase kbclientserver KB203638