How to create a stored procedure sql server to see all tables in a databse?

Question

So far in the website I have only found stored procedures to list all the tables in a database, but what I really want to know is how to create a stored procedure in SQL Server to display all data of all tables in a specific database.

I don't want to join the tables and then display a huge table, I want to create some kind of loop that takes the first table of the database, performs a

SELECT * FROM <Table>

query, and then continues with the next table of the database and so on until all tables are displayed.

I know it should be easy but I have never created a stored procedure before so I don't know how to use the variables or go through the tables.

Thank you

There's a reason you haven't found this, it's because it's more than inadvisable. Why are you trying to do this? — Tony Hopkinson
– Tony Hopkinson, Commented Dec 1, 2011 at 16:11
Are you sure that you really want to get back all of the data in all of the tables within a database? What is going to handle the output? — ChrisBD
– ChrisBD, Commented Dec 1, 2011 at 16:13
possible duplicate of In SQL Server, I want to use the same query for all the tables in a database — Joe Stefanelli
– Joe Stefanelli, Commented Dec 1, 2011 at 16:15

guest · Accepted Answer · 2011-12-01 16:53:09Z

1

Something like this should work:

CREATE Procedure [dbo].[procSelectAllFromAllTables]
AS

DECLARE @table nvarchar(500)
DECLARE @sql nvarchar(520)

DECLARE CursorSelect CURSOR FOR
    select table_name from INFORMATION_SCHEMA.tables where table_name not like 'sys%'

OPEN CursorSelect
FETCH NEXT FROM CursorSelect
INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'select * from ' + @table
    exec(@sql)
    FETCH NEXT FROM CursorSelect
    INTO @table     
END
CLOSE CursorSelect
DEALLOCATE CursorSelect
RETURN

answered Dec 1, 2011 at 16:53

guest

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:27:09Z

0

learn how to create a stored procedure
learn how to use variables in a stored procedure
Get a list of all the table names
use a cursor to create a while loop on a list of all table names
use dynamic sql on 'select * from ' + @tablename

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Dec 1, 2011 at 16:38

JeffO

8,1233 gold badges46 silver badges55 bronze badges

Comments

Stephen Wuebker · Accepted Answer · 2015-10-29 20:29:28Z

0

As others have said, this is a silly idea from a practical standpoint, but as an academic exercise, it is fairly simple if you use a bit of dynamic sql and COALESCE(). No cursors or loops required.

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL, '') + ' SELECT * FROM ' + s.name + '.' + t.name
FROM sys.tables t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id

EXEC @SQL

answered Oct 29, 2015 at 20:29

Stephen Wuebker

1896 bronze badges

Collectives™ on Stack Overflow

How to create a stored procedure sql server to see all tables in a databse?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related