如何返回有关 SQL Server 链接服务器的信息

摘要

SQL server 7.0 和更高版本的 SQL Server 允许将外部数据源添加为链接服务器。 此功能提供对 OLE DB 数据源的分布式异类查询的访问权限。 当连接到 SQL Server 时，可以使用 SQLTables （file_handle）命令返回一个列出可用表的光标。 但是，SQLTables （）命令不会返回有关链接服务器中可用的表的信息。 有关链接服务器的信息由三个不同的 SQL Server 存储过程返回。 本文介绍如何使用这些存储过程返回有关链接服务器中可用的表的信息。

更多信息

SQL Server 链接服务器定义为针对特定链接服务器的映射。 因此，链接服务器可能不一定是 SQL Server 数据库的一部分。SQL Server 链接服务器使用 sp_addlinkedserver 系统存储过程进行定义。 链接服务器定义包含查找 OLE DB 数据源所需的所有信息。 然后，可以通过两种方式引用来自链接服务器名称的远程表：

• 链接服务器名称可以用作在 Transact-sql 语句中用作表或视图引用的四部分名称中的服务器名称。 名称的其他三部分引用作为行集公开的链接服务器中的对象。

• 链接服务器名称可用作 OPENQUERY 函数的输入参数。 OPENQUERY 将向 OLE DB 提供程序发送要执行的命令。 然后，返回的行集可用作 Transact-sql 语句中的表或视图引用。

用于返回有关链接服务器的信息的 SQL Server 存储过程包括：

• sp_linkedservers

• sp_tables_ex

• sp_columns_ex

每个 SQL Server 存储过程都返回与链接服务器相关的不同级别的详细信息。 sp_linkedservers 返回以下列： 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 返回有关指定链接服务器上的表的信息。 此存储过程接受下列参数： 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. sp_tables_ex 返回以下列： 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. 对于给定的链接服务器表，sp_columns_ex 返回列信息，每列一行。 如果指定了 column，则 sp_columns_ex 仅返回给定列的列信息。 此存储过程接受下列参数： 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. sp_columns_ex 返回以下列： 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.

1. 打开 ODBC 管理器并创建一个名为 "LINKSRV" 的 ODBC 数据源。 注意 由于链接服务器在 "SQL Server 安全" 选项卡中列出，因此无需在创建 DS 时设置默认数据库。

2. 使用以下代码创建名为 SP_tables "prg" 的程序文件：

      * 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.

3. 在 "命令" 窗口中，键入以下内容： DO SP_TABLES

参考

Transact-sql 参考帮助;搜索： "sp_addlinkedserver"; "sp_tables_ex";"sp_linkedservers";"sp_columns_ex"