从不同的数据库获取表的所有列

s there a syntax wherein I can get the columns of a table from a different database? I have tried using select column_name from information_schema.columns where table_name = 'Database_Name.DBO.Table_Name' But it's not working. Please do help me, been doing this for days.

有没有一种语法可以让我从不同的数据库中获取表的列?我尝试过使用information_schema中的column_name。表_name = 'Database_Name.DBO的列。Table_Name'，但是不工作。请帮帮我，我已经这样做了好几天了。

3 个解决方案

#1

You're close. TABLE_NAME has just the name of the table. The rest of the information is in TABLE_CATALOG and TABLE_SCHEMA. You can qualify information_schema with the catalog you want to select from:

你关闭。TABLE_NAME只包含表的名称。其余的信息在TABLE_CATALOG和TABLE_SCHEMA中。您可以使用要从以下目录中选择的目录对information_schema进行限定:

select column_name from Database_Name.information_schema.columns
where table_name = 'Table_Name' AND table_schema = 'dbo'

#2

The table names in the information_schema.columns system table do not have the Database_Name.DBO.Table_Name format. They just have the Table name as a varchar value. So try it like this:

information_schema中的表名。列系统表没有Database_Name.DBO。Table_Name格式。它们只是将表名作为varchar值。试试这样:

USE Database_Name;
select column_name from information_schema.columns
where table_name = 'Table_Name'

#3

This is what I found out. Each database has its own information_schema view so it won't show the information from the other databases. So if you want to get the column names for a table in another database, you have to use the information_schema view of that database.

这就是我发现的。每个数据库都有自己的information_schema视图，因此不会显示来自其他数据库的信息。因此，如果您想获取另一个数据库中的表的列名，您必须使用该数据库的information_schema视图。

{database name}.information_schema

{数据库名称} .information_schema

Example:

例子:

SET @Sql = CONCAT('INSERT INTO ', @DBName, '.dbo.colname_table (column_name) 
    SELECT COLUMN_NAME FROM ', @DBName, '.INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = ''table_name'' AND TABLE_SCHEMA = ''dbo'' 
    ORDER BY ORDINAL_POSITION');

EXECUTE sp_executesql @Sql;

where @DBName is the name of the database.

其中@DBName是数据库的名称。

#1