从SQL Server 2008中的所有表中选择所有列

How can I Select all columns from all tables from the DB, like:

如何从DB中的所有表中选择所有列，如:

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?

表列表´s非常大,有这么多列,有可能在不编写列名的情况下执行它吗?

Or maybe make a select that returns the name of the tables.

或者做一个选择，返回表的名称。

5 个解决方案

#1

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)

#2

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

#3

Try this, works fine

试试这个,没问题

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

then you could add

然后你可以添加

WHERE TABLE_NAME LIKE '' AND COLUMN_NAME LIKE ''

#4

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中检索所有列的名称。可以从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.

但是不可能从所有表中检索所有数据。只要查询中涉及多个表，就需要连接。除非提供联接条件，否则表将作为完整的笛卡尔积进行联接，这意味着每个表中的每一行与来自其他所有表的每一行进行匹配。这样的查询可以生成10个表，每个表的记录不少于10e10条记录。1000亿条记录。我肯定你不想要这个。

Perhaps if you explain what you what to 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 ?'

#5

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，我建议您使用export向导，并简单地选择那里的所有表。在对象浏览器中，将光标放在数据库名称上，然后右击。选择任务——导出数据并跟踪向导。我不明白为什么有人想要Excel中的整个数据库，但这是最好的方法。如果需要多次执行，可以将导出保存在SSIS包中。

#1