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 NVarChar(MAX)

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

EXEC (@SQL)