Get random row sample data from all user databases/tables across multiple server

libpekin 166 Reputation points
2022-10-24T18:55:52.837+00:00

Hello,

I am using the query below to pull data from all tables in a database. However, I'd like to expand the functionality to "get a random row sample (1000 rows) of data from all tables in all databases across multiple servers. Additionally, with the current query

Thanks for your assistance and time.

*DECLARE @alenzi VARCHAR(MAX)
SET @alenzi = ''
SELECT @alenzi = @alenzi + 'SELECT '''+ QUOTENAME(name) + '''; SELECT * FROM ' + QUOTENAME(name) + CHAR(13) FROM sys.tables
EXEC(@alenzi );*

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,991 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,656 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 112.7K Reputation points MVP
    2022-10-24T21:32:36.503+00:00

    To get 1000 random rows from a table, the standard trick is

       SELECT TOP 1000 * FROM tbl ORDER BY newid()  
    

    Bear in mind that for a big table, SQL Server will read all rows, so it can be expensive. A leaner alternative is

       SELECT * FROM tbl TABLESAMPLE (1000 ROWS)  
    

    But this is less random, as SQL Server will read all rows on a couple of pages. And the number of rows may not be exactly 1000.


  2. LiHongMSFT-4306 27,961 Reputation points
    2022-10-25T07:21:06.817+00:00

    Hi @Francis-3079
    If you don't mind the execute time, then try this solution using Cursor:

    DECLARE RandomData_Cursor CURSOR SCROLL  
    FOR SELECT name FROM sys.tables ORDER BY NEWID()  
      
    OPEN RandomData_Cursor  
    DECLARE @TableName VARCHAR(200)  
    DECLARE @SQL VARCHAR(MAX)  
    DECLARE @Counter INT = 1  
      
    FETCH FIRST FROM RandomData_Cursor INTO @TableName  
    WHILE @@fetch_status=0 AND @Counter <= 1000  
    BEGIN  
       SELECT @SQL = @SQL+'SELECT TOP 1 '''+@TableName+ '''AS Table_Name,* FROM '+@TableName+' ORDER BY NEWID()'  
       --PRINT @SQL  
       EXEC(@SQL)  
       FETCH NEXT FROM RandomData_Cursor INTO @TableName  
       SET @Counter += 1  
       SET @SQL=''  
    END  
      
    DEALLOCATE RandomData_Cursor  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  3. daisuke yanagi 1 Reputation point
    2022-11-02T04:56:37.32+00:00

    Hi @libpekin

    If you want to select all tables with the same query, you can use the "sp_MSforeachtable" stored procedure.
    Replace 'A' with the user table name and execute the query.

    exec sp_MSforeachtable N'  
    select top 1000 * from ? order by newid()  
    '  
    

    It is an undocumented stored procedure. Use with caution.

    「It is strongly recommended to avoid using undocumented features of SQL Server in your Production environment.」
    From Microsoft.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.