CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
user185953
user185953

Reputation:

Select all columns from all tables in SQL Server 2008

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.

Upvotes: 15

Views: 105715

Answers (6)

David
David

Reputation: 34543

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)

Upvotes: 38

Ganesh
Ganesh

Reputation: 59

In SQL Server 2016 Management Studio ( Version: 13.0.15900.1), to get all column names in a specified table, below is the syntax:

   **Select name from [YourDatabaseName].[sys].[all_columns] 
   where object_id=(Select object_id from [YourDatabaseName].[sys].[tables] 
   where name='YourTableName')**

Upvotes: 3

NeshaSerbia
NeshaSerbia

Reputation: 2542

Try this, works fine

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

then you could add

WHERE TABLE_NAME LIKE '' AND COLUMN_NAME LIKE ''

Upvotes: 17

Ashfaq Shaikh
Ashfaq Shaikh

Reputation: 1668

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

Upvotes: 10

HLGEM
HLGEM

Reputation: 96542

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.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294177

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

Upvotes: 3

Related Questions