Select all columns from all tables in SQL Server 2008

Question

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

Select * From *

in 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?

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

Answer 1

This SQL will do this...

DECLARE @SQL AS VarChar(MAX)
SET @SQL = ''

SELECT @SQL = @SQL + 'SELECT * FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES

EXEC (@SQL)

Answer 2

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

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.

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 'select  * from ?'

Answer 3

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.