如何列出所有表格SQL Server中链接服务器数据库的列名称? [英] How to list all tables & columns names of a linked-server database in SQL Server?

问题描述

如果它是常规数据库，我可以简单地使用此查询来获取数据库的所有表名及其列名的列表.

If it's a regular database, i can simply use this query to get a list of all table names and their column names of the database.

use [my_database_name]
GO

SELECT sys.tables.name AS Table_Name, 
       sys.columns.name AS Column_Name, 
       sys.columns.max_length, 
       (schema_id) As Schema_name

FROM sys.tables
    INNER JOIN sys.columns 
        ON sys.tables.OBJECT_ID=sys.columns.object_id

ORDER BY schema_name, sys.tables.name, sys.columns.name

但是现在我需要连接到链接服务器数据库，因此不能使用"use". 还有另一种方法吗?

but right now I need to connect to a linked-server database therefore the 'use' can't be used. Is there another way?

推荐答案

在@scsimon的答案上稍作扩展...

To expand slightly on @scsimon's answer...

(schema_id) As Schema_name并不是很有用，因为它实际上是一个ID.要获得具有实际模式名称的所有表(及其列)的列表，可以使用:

The (schema_id) As Schema_name is not very useful, as it's really an ID. To get the list of all tables (and their columns) with actual schema names one can use:

SELECT s.name AS schema_name
      ,t.name AS table_Name
      ,c.name AS column_Name
    --,c.max_length
FROM [SERVER].[DB].sys.tables t
JOIN [SERVER].[DB].sys.schemas s ON t.schema_id = s.schema_id
JOIN [SERVER].[DB].sys.columns c ON t.object_id = c.object_id
--WHERE s.name = 'dbo'
ORDER BY s.name, t.name, c.name

这篇关于如何列出所有表格SQL Server中链接服务器数据库的列名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！