从 SQL Server 2008 中的所有表中选择所有列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1534147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select all columns from all tables in SQL Server 2008
提问by David
How can I Select all columns from all tables from the DB, like:
如何从数据库中的所有表中选择所有列，例如：
Select * From *
in SQL Server 2008???
在 SQL Server 2008 中？？？
The table list it′s very very big, and have so many columns, is it possible to do it without writing the column names?
表list非常非常大，有这么多列，不写列名可以吗？
Or maybe make a select that returns the name of the tables.
或者也许做一个返回表名称的选择。
回答by David
This SQL will do this...
此 SQL 将执行此操作...
DECLARE @SQL AS VarChar(MAX)
SET @SQL = ''
SELECT @SQL = @SQL + 'SELECT * FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
EXEC (@SQL)
回答by Ashfaq Shaikh
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID where t.name = 'ProductItem' AND C.name like '%retail%'
ORDER BY schema_name, table_name
回答by NeshaSerbia
Try this, works fine
试试这个，效果很好
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
then you could add
那么你可以添加
WHERE TABLE_NAME LIKE '' AND COLUMN_NAME LIKE ''
回答by Remus Rusanu
It is possible to retrieve the name of all columns from sys.columns
It is possible to retrieve the name of all table from sys.tables
可以从sys.columns 中检索所有列
的名称 可以从sys.tables 中检索所有表的名称
But is impossible to retrieve all the data from all the tables. As soon as more than one table is involved in a query, a JOIN is necessary. Unless join conditions are provided, tables are joined as full Cartesian product, meaning each row from each table is matched with each row from ll other tables. Such a query as you request would produce for 10 tables with 10 records each no less than 10e10 records, ie. 100 billion records. I'm sure you don't want this.
但是不可能从所有表中检索所有数据。一旦查询中涉及多个表，就需要 JOIN。除非提供连接条件，否则表将作为完整的笛卡尔积连接，这意味着每个表中的每一行都与所有其他表中的每一行匹配。您请求的此类查询将生成 10 个表，每个表有 10 条记录，每条记录不少于 10e10 条记录，即。1000 亿条记录。我确定你不想要这个。
Perhaps if you explain what you whatto achieve, not how, we can help better.
也许如果你解释你要实现什么，而不是如何实现，我们可以更好地提供帮助。
To select * from each table, one after another, you can use the undocumented but well known sp_msforeachtable:
要从每个表中一个接一个地选择 *，您可以使用未公开但众所周知的 sp_msforeachtable：
sp_msforeachtable 'select * from ?'
回答by HLGEM
If you are going to send to Excel, I would suggest you use the export wizard and simply select all the tables there. In the object browser, put your cursor on the database name and right click. Chose Tasks - Export Data and follow the wizard. WHy anyone would want an entire database in Excel is beyond me, but that's the best way. If you need to do it more than once you can save the export in an SSIS package.
如果您要发送到 Excel，我建议您使用导出向导并简单地选择那里的所有表。在对象浏览器中，将光标放在数据库名称上并右键单击。选择任务 - 导出数据并按照向导操作。为什么有人想要 Excel 中的整个数据库超出我的范围，但这是最好的方法。如果您需要多次执行此操作，您可以将导出保存在 SSIS 包中。
回答by Ganesh
In SQL Server 2016 Management Studio ( Version: 13.0.15900.1), to get all column names in a specified table, below is the syntax:
在 SQL Server 2016 Management Studio（版本：13.0.15900.1）中，获取指定表中的所有列名，语法如下：
**Select name from [YourDatabaseName].[sys].[all_columns]
where object_id=(Select object_id from [YourDatabaseName].[sys].[tables]
where name='YourTableName')**
