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.

That doesn't really make sense. What exactly do you expect to get back from such a query? — Jim Garrison, Oct 07 '09 at 21:00
If you wanted to inject SQL, that is one of the simpler things you could inject to get back the schema meta data comes to mind. To actually do it requires a statement using system views etc, but you see what I mean. — Andrew, Oct 07 '09 at 21:04
Well, don't really know, I was asked to do this... Actually they need the data In Excel, but it's table by table, so I need to automate the query... — , Oct 07 '09 at 21:06
This may help. http://kb.softescu.ro/programming/p-tsql/select-all-columns-from-all-tables-in-the-database/ — danicode, May 02 '12 at 13:41
legitimate reason for wanting this: i want to do this because I am clearing out a database to start again with a new client. it has some data i want to keep, and a lot to delete. i don't want to mess about with scripts to create a clean instance so this is a easy way to ensure i have deleted everything correctly — Simon_Weaver, Apr 20 '14 at 01:39
@DavidBasarab old question but I thought I'd give my reason for wanting this: I have a test that is failing if I run a specific test before it, even though every test is within a transaction and "should" be rolled back when finishing no matter the result. This makes me wanting an easy way to check the content of the whole database before and after each test to see if I can figure out what is happening. — Martin Riddar, Sep 24 '20 at 06:58

score 38 · Accepted Answer · edited Apr 20 '14 at 01:32

38

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)

edited Apr 20 '14 at 01:32

Simon_Weaver

140,023
84
646
689

answered Oct 07 '09 at 21:05

David

34,223
3
62
80

1

how would you print a `table_name` for each result? – angularrocks.com Aug 01 '17 at 04:54
1

@Kuncevic, If you want to include the table name in each resultset, just add it into the @@SQL (Replace the double "at" signs with a single one): SELECT @@SQL = @@SQL + 'SELECT ''' + TABLE_NAME + ''' AS TableName, * FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']' + CHAR(13) FROM INFORMATION_SCHEMA.TABLES – David Aug 02 '17 at 16:58
ahh I was doing exactly same thing but using 1 single quote `' + TABLE_NAME + '` - didn't work. instead of using 3 `''' + TABLE_NAME + '''` - that works. – angularrocks.com Aug 02 '17 at 23:10

score 15 · Answer 2 · answered Mar 10 '14 at 08:11

15

Try this, works fine

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

then you could add

WHERE TABLE_NAME LIKE '' AND COLUMN_NAME LIKE ''

answered Mar 10 '14 at 08:11

NeshaSerbia

2,324
2
14
13

score 10 · Answer 3 · answered Dec 26 '11 at 11:04

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

score 3 · Answer 4 · edited Oct 08 '09 at 05:37

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

score 3 · Answer 5 · answered Aug 16 '18 at 10:56

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')**

score 2 · Answer 6 · answered Oct 08 '09 at 13:14

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.

Select all columns from all tables in SQL Server 2008

6 Answers6

Linked