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

Francis, W 66 Reputation points


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.

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.
11,635 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,462 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 93,131 Reputation points

    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. CosmogHong-MSFT 17,621 Reputation points Microsoft Vendor

    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 @Counter INT = 1  
    FETCH FIRST FROM RandomData_Cursor INTO @TableName  
    WHILE @@fetch_status=0 AND @Counter <= 1000  
       SELECT @SQL = @SQL+'SELECT TOP 1 '''+@TableName+ '''AS Table_Name,* FROM '+@TableName+' ORDER BY NEWID()'  
       --PRINT @SQL  
       FETCH NEXT FROM RandomData_Cursor INTO @TableName  
       SET @Counter += 1  
       SET @SQL=''  
    DEALLOCATE RandomData_Cursor  

    Best regards,

    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

    Hi @Francis, W

    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