Select all tables in a database using T-SQL and a WHILE loop?

Question

I am attempting to return all table results from each table in my database AdventureWorksDW2019. Would my current approach work somehow? Or is there more simple/efficient way to do this? In my current approach, I am storing all of the table names in a temp table with their respective row number when sorted by ascending name. Then, I am trying to wrap that in a WHILE statement to loop through each table and select all results from each table. Any advice would be greatly appreciated!

DROP TABLE IF EXISTS  #TableNamesSorted

SELECT
    name,
    RowNum = ROW_NUMBER() OVER(ORDER BY name)
    INTO #TableNamesSorted
    FROM
        SYSOBJECTS
    WHERE
        xtype = 'U'

DECLARE @i INT = 0;
DECLARE @currentTableName varchar(25);

WHILE @i < (SELECT COUNT(*) FROM #TableNamesSorted)
BEGIN
    SET @i = @i + 1
    SET @currentTableName = (SELECT name from #TableNamesSorted WHERE RowNum = @i)
    SELECT * FROM (SELECT @currentTableName)
END

Would it work? Why not try it and see? That would be much quicker than asking us, and more reliable. — Dale K
– Dale K, Commented Jun 17, 2022 at 2:11
the error Incorrect syntax near the keyword 'END' is due to you did not assign an alias to the derived table SELECT * FROM (SELECT @currentTableName) — Squirrel
– Squirrel, Commented Jun 17, 2022 at 2:15
@Squirrel That's just the start of the problems. You cannot use a variable as a table name in this manner. This will require dynamic sql. — SMor
– SMor, Commented Jun 17, 2022 at 2:17
Better idea: sp_executesql with a string from SELECT STRING_AGG( CONCAT( 'SELECT * FROM ', TABLE_NAME, ';' ), CONCAT( CHAR(13), CHAR(10) ) ) FROM INFORMATION_SCHEMA.TABLES — Dai
– Dai, Commented Jun 17, 2022 at 2:27
@SeanLange The 8000-char limit doesn't apply if you use it with an explicit CONVERT( varchar(max), ): stackoverflow.com/questions/71355661/… — Dai
– Dai, Commented Jun 17, 2022 at 4:26

Sean Lange · Accepted Answer · 2022-06-17 03:33:45Z

3

I absolutely abhor loops when they are not needed and this is one of those times. You can easily generate a string with a select top 10 from each table. Please notice this will also handle schemas which if you have more than one schema your solution would fail. I also included the name of the table as the first column so you know what table you are looking at sample data for. It is this simple, no temp tables, no loops.

declare @sql nvarchar(max) = ''

select @sql += 'select top 10 TableName = ''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''', * from  ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
from sys.tables t
join sys.schemas s on s.schema_id = t.schema_id

exec sp_executesql @sql

answered Jun 17, 2022 at 3:33

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

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

Comments

Sean P · Accepted Answer · 2022-06-17 02:57:55Z

0

Although there are likely better means to go about this (thank you Dai and SMor),

I got it to work using this:

 IF OBJECT_ID('tempdb.#TableNamesSorted') IS NULL DROP TABLE #TableNamesSorted

SELECT
    name,
    RowNum = ROW_NUMBER() OVER(ORDER BY name)
    INTO #TableNamesSorted
    FROM
        SYSOBJECTS
    WHERE
        xtype = 'U'

DECLARE @i INT = 0;
DECLARE @currentTableName varchar(25);


DECLARE @SQL nvarchar(1000)
DECLARE @TableCount INT = (SELECT COUNT(*) FROM #TableNamesSorted)
WHILE @i <=  @TableCount
BEGIN TRY

    SET @i = @i + 1
    SET @currentTableName = (SELECT name from #TableNamesSorted WHERE RowNum = @i)
    
    SET @SQL = CONCAT('SELECT TOP 10 * FROM ', @currentTableName)
    EXEC (@SQL)

END TRY
BEGIN CATCH  
Print 'Errors on ' + @currentTableName 
END CATCH;

answered Jun 17, 2022 at 2:57

Sean P

455 bronze badges

3 Comments

SMor Over a year ago

One can easily argue that selecting all rows from all tables is not a useful method "to familiarize myself with the database's design". Normally one looks at the DDL and the relationships between tables. Until you understand what the tables actually represent as entities in a model, looking vast amounts of data seems a bit strange.

Dai Over a year ago

@SMor On the contrary: too-often databases are underdocumented - and even if the foreign-key relationships make-sense seeing the definition of a column won't really tell you exactly what it contains, e.g. if you have a column named Name you need to see a sample of the data in the table to see if it's a first-name, last-name, all-upper-case, etc. Seeing sample data is as important as the DDL, imo.

HABO Over a year ago

Tip: The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space or reserved words like From.

Collectives™ on Stack Overflow

Select all tables in a database using T-SQL and a WHILE loop?

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related