title: How to select top 10 records form all tables in a database and test for nulls in each field?
body: <p>I'm trying to run the code below to select the top 10 records from each table in my database.  From this subset, I want to find the percent age of nulls for each field in each table.  This code runs, but it gives me 10 records, not 10 from each table.  How can I modify this code to get the results I'm after?</p>
  
  <pre><code>DROP TABLE Columns
  
  DECLARE @schemaName AS sysname;
  DECLARE @tableName AS sysname;
  DECLARE @columnName AS sysname;
  DECLARE @schema_id AS int;
  DECLARE @object_id AS int;
  DECLARE @column_id AS int;
  DECLARE @isNullable AS bit;
  DECLARE @lastSchema_id AS int;
  DECLARE @lastTable_id AS int;
  DECLARE @recordCount AS bigint;
  DECLARE @nullCnt AS bigint;
  DECLARE @SQL as nvarchar(max);
  DECLARE @paramDefinition NVARCHAR(max);
  
  if exists(select name from tempdb..sysobjects where name LIKE'Columns%')
  DROP TABLE Columns;
  
  CREATE TABLE Columns (
      schema_id int,
      object_id int,
      column_id int,
      schemaName sysname,
      tableName sysname,
      columnName sysname,
      recordCnt bigint,
      nullCnt bigint,
      nullPct numeric(38,35) );
  
  -- Set to the @lastSchema_id and @lastTable_id to NULL so that the first 
  --  loop through the cursor the record count is generated.
  SET @lastSchema_id = NULL;
  SET @lastTable_id = NULL;
  
  -- List of all the user schemas.tables.columns
  --  in the database
  DECLARE c_Cursor CURSOR FOR
  SELECT top (10)  schemas.schema_id
       , all_objects.object_id
       , all_columns.column_id
       , schemas.name AS schemaName
       , all_objects.name AS tableName
       , all_columns.name AS columnName
       , all_columns.is_nullable
   FROM sys.schemas
   INNER JOIN sys.all_objects
      ON schemas.schema_id = all_objects.schema_id
     AND all_objects.type = 'U'
   INNER JOIN sys.all_columns
      ON all_objects.object_id = all_columns.object_id
   ORDER BY schemas.schema_id
       , all_objects.object_id
       , all_columns.column_id;
  
  OPEN c_Cursor;
  
  FETCH NEXT FROM c_Cursor
   INTO @schema_id
      , @object_id
      , @column_id
      , @schemaName
      , @tableName
      , @columnName
      , @isNullable;
  
  -- Loop through the cursor
  WHILE @@FETCH_STATUS = 0
  BEGIN
  
      -- Get the record count for the table we are currently working on if this is
      --  the first time we are encountering the table.
      IF ( ( @schema_id &lt;&gt; @lastSchema_id ) OR ( @object_id &lt;&gt; @lastTable_id )
        OR ( @lastSchema_id IS NULL ) OR ( @lastTable_id IS NULL ) )
      BEGIN
  
          SET @SQL = N'SELECT top (10) @recordCount = COUNT(1) FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + ';';
          SET @paramDefinition = N'@recordCount bigint OUTPUT';
  
          exec sp_executesql @SQL,
                             @paramDefinition,
                             @recordCount = @recordCount OUTPUT;
  
      END
  
      -- If the column is NOT NULL, there is no reason to do a count
      --  Set the nullCnt and nullPct to 0
      IF ( @isNullable = 0 )
      BEGIN
  
  
          INSERT INTO Columns
               ( [schema_id]
               , [object_id]
               , column_id
               , schemaName
               , tableName
               , columnName
               , recordCnt
               , nullCnt
               , nullPct )
          VALUES
               ( @schema_id
               , @object_id
               , @column_id
               , @schemaName
               , @tableName
               , @columnName
               , @recordCount
               , 0
               , 0.0 );
      END
      -- If the column is NULL, count the number of NULL fields in the table.
      ELSE
      BEGIN
  
          SET @SQL = N'SELECT top (10) @nullCnt = COUNT(1) FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + 
                     N' WHERE ' + QUOTENAME(@columnName) + N' IS NULL;';
          SET @paramDefinition = N'@nullCnt bigint OUTPUT';
  
          PRINT @SQL;
  
          exec sp_executesql @SQL,
                             @paramDefinition,
                             @nullCnt = @nullCnt OUTPUT;
  
          INSERT INTO Columns
               ( [schema_id]
               , [object_id]
               , column_id
               , schemaName
               , tableName
               , columnName
               , recordCnt
               , nullCnt
               , nullPct )
          VALUES
               ( @schema_id
               , @object_id
               , @column_id
               , @schemaName
               , @tableName
               , @columnName
               , @recordCount
               , @nullCnt
               -- USE NULLIF in case there are no recods in the table
               , ISNULL( @nullCnt * 1.0 / NULLIF( @recordCount, 0) * 100.0, 0 ) );
  
      END
  
      -- Set the @lastSchema_id and @lastTable_id so that on 
      --  the next loop, if it's the same table there is no 
      --  need to recount the columns for the table.
      SET @lastSchema_id = @schema_id;
      SET @lastTable_id = @object_id;
  
      FETCH NEXT FROM c_Cursor
       INTO @schema_id
          , @object_id
          , @column_id
          , @schemaName
          , @tableName
          , @columnName
          , @isNullable;
  
  END;
  
  CLOSE c_Cursor;
  DEALLOCATE c_Cursor;
  
  SELECT *
    FROM Columns;
  </code></pre>