USP_RandomLoad

Script to generate a random load by multiplying the results of 2 random tables! -

CREATE PROCEDURE USP_RandomLoad AS
/*
Util    : USP_RandomLoad

Purpose : Generate Random load on a server.

How? :    Randomly selects 2 tables from current database
          and multiply their results together.

   It could easily generate no load if one of the tables
   contains no rows.

Use :     EXECUTE USP_RandomLoad

    Place the procedure in a loop to execute multiple times
    and purposely 'stress' a server.
*/
SET NOCOUNT ON
/* Declare Variables */
DECLARE @SQL VARCHAR(200)
DECLARE @TableCount INT
DECLARE @InnerTableNumber INT
DECLARE @InnerCounter INT
DECLARE @OuterTableNumber INT
DECLARE @OuterCounter INT
DECLARE @VarTable1Schema VARCHAR(200)
DECLARE @VarTable2Schema VARCHAR(200)
DECLARE @VarTable1Name VARCHAR(200)
DECLARE @VarTable2Name VARCHAR(200)

/* Get the total number of tables */
SELECT @TableCount = COUNT(*) FROM INFORMATION_SCHEMA.TABLES

/* Randomly select a outer table by table number */
SELECT @OuterTableNumber = ROUND(RAND() * @TableCount,0,1)
IF @OuterTableNumber = 0 SET @OuterTableNumber = 1
IF @OuterTableNumber > @TableCount SET @OuterTableNumber = @TableCount

SET @OuterCounter = 0

/* Declare a cursor and iterate through all the 
   tables in the current database in tablename order. */
DECLARE CURSORNAME CURSOR FOR
SELECT TABLE_SCHEMA,TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
    SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME
OPEN CURSORNAME
FETCH NEXT FROM CURSORNAME 
INTO @VarTable1Schema, @VarTable1Name
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @OuterCounter = @OuterCounter + 1
 IF @OuterCounter = @OuterTableNumber
 /* Random Table found */
  BEGIN
  SET @InnerCounter = 0

  /* Randomly select a inner table by table number */
  SELECT @InnerTableNumber = ROUND(RAND() * @TableCount,0,1)
  IF @InnerTableNumber = 0 SET @InnerTableNumber = 1
  IF @InnerTableNumber > @TableCount SET @InnerTableNumber = @TableCount

  /* Inner cursor to loop through the tables until it gets
     to the Nth one, i.e. the one that corresponds to the 
     random number created */
  DECLARE INNERCURSORNAME CURSOR FOR
  SELECT TABLE_SCHEMA,TABLE_NAME 
  FROM INFORMATION_SCHEMA.TABLES
   SELECT *  FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME
  OPEN INNERCURSORNAME
  FETCH NEXT FROM INNERCURSORNAME 
  INTO @VarTable2Schema, @VarTable2Name
  WHILE @@FETCH_STATUS = 0
  BEGIN
   SET @InnerCounter = @InnerCounter + 1
   IF @InnerCounter = @InnerTableNumber
    /* Random Table found */
    BEGIN
    /* Generate a random recordset by using CROSS APPLY to multiply the 2 tables. 
       The record set could be ANY size hence a totally random load is generated. */
    SET @SQL = 'SELECT * FROM ['+@VarTable1Schema+'].['+@VarTable1Name+'] TABLE1 CROSS APPLY ['+@VarTable2Schema+'].['+@VarTable2Name+'] TABLE2 ORDER BY 2 DESC'
    BEGIN TRANSACTION
     --PRINT CAST(@OuterTableNumber AS VARCHAR(5))
     --PRINT CAST(@InnerTableNumber AS VARCHAR(5))
     PRINT (@SQL)
     SET NOCOUNT OFF
     EXEC (@SQL)
     --RETURN
    COMMIT TRANSACTION
    END
  FETCH NEXT FROM INNERCURSORNAME 
  INTO @VarTable2Schema, @VarTable2Name
  END
  CLOSE INNERCURSORNAME
  DEALLOCATE INNERCURSORNAME
  -- INNER CURSOR END
  END

FETCH NEXT FROM CURSORNAME 
INTO @VarTable1Schema, @VarTable1Name
END
CLOSE CURSORNAME
DEALLOCATE CURSORNAME

GO