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

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

CodePudding user response：

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

CodePudding user response：

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;