-1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1073816
  • 71
  • 1
  • 5
  • 2
    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 Dec 01 '11 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 Dec 01 '11 at 16:13
  • possible duplicate of [In SQL Server, I want to use the same query for all the tables in a database](http://stackoverflow.com/questions/8329661/in-sql-server-i-want-to-use-the-same-query-for-all-the-tables-in-a-database) – Joe Stefanelli Dec 01 '11 at 16:15
  • Yes, it is a small database, its just for academic purposes – user1073816 Dec 01 '11 at 16:20

3 Answers3

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
guest
  • 11
  • 1
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
Stephen Wuebker
  • 189
  • 6
0
  1. learn how to create a stored procedure
  2. learn how to use variables in a stored procedure
  3. Get a list of all the table names
  4. use a cursor to create a while loop on a list of all table names
  5. use dynamic sql on 'select * from ' + @tablename
Community
  • 1
  • 1
JeffO
  • 7,957
  • 3
  • 44
  • 53