Limit SQL query to only scan tables with less than 500 records

Question

Ive created the following script which goes through all values in all fields of my database.

However this is a extremely time consuming task so i wish to limit it to only go through the tables with 500 or less records and skip the large tables that are filled with irrelevant data anyways.

Im having issues figuring out how to include my COUNT on the table since im working off a varchar(256) as table name.

USE [Name of Database]
DECLARE @SearchStr nvarchar(100) = 'Text to search fields for'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

Any help or suggestions to achieve this would be greatly appreciated?

Hi. Slightly off-topic but worth mentioning. There is a [very strong case against using the NoLock hint](https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/). TL/DR; NoLock can return rows that do not exist and omit others that do. — David Rushton, Nov 09 '16 at 09:39
A good read. However it will not impact the result of what im trying to achieve with this query. Duely noted though. — AronChan, Nov 09 '16 at 09:45

score 1 · Accepted Answer · edited May 23 '17 at 12:07

You can use an approximate way with sys.dm_db_partition_stats table. Just add the JOIN as in this code :

    ...
    SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES

            --add this JOIN 
            JOIN 
              (
                SELECT
                   object_name(object_id) as Table_Name
                FROM sys.dm_db_partition_stats
                WHERE (index_id < 2)
                GROUP BY object_name(object_id)
                HAVING SUM(row_count)<=500
              ) as Stat
             ON  INFORMATION_SCHEMA.TABLES.TABLE_NAME = Stat.Table_Name
             -- add this JOIN 


             WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )
    ...

More details: Fastest way to count exact number of rows in a very large table?

Excellent. That was exactly what i needed! – AronChan Nov 09 '16 at 12:15 — AronChan, Nov 09 '16 at 12:15

Ranjana Ghimire · Answer 2 · 2016-11-09T09:10:33.377

0

All you need to do is calculate count of a table and apply if condition like

if(select count(*) from table_name)<500
begin
    execute your query
end

edited Nov 09 '16 at 09:10

answered Nov 09 '16 at 09:08

Ranjana Ghimire

1,785
1
12
20

But then i would still get 500 records from all the tables that i am basically not interested in. I wish to skip going through the table at all if it contains more than 500 records. Thanks for the input! – AronChan Nov 09 '16 at 09:10
The problem is i never really have a table variable to execute the count on. Am i missing something obvious? – AronChan Nov 09 '16 at 09:18
As you have table variable, you can easily create above query and store it into some temporary table than call the table value so as to compare with <500 – Ranjana Ghimire Nov 09 '16 at 09:30
But i dont have a variable. Since it is dynamic SQL there is never really any variable in play as far as i know. This explains my issue i think: http://www.sommarskog.se/dynamic_sql.html#Dyn_table – AronChan Nov 09 '16 at 09:37

score 0 · Answer 3 · answered Nov 09 '16 at 11:05

You can use the system table sys.dm_db_partition_stats to find tables with 500+ records.

/* Returns tables with 500+ rows, 
 * using the system tables.
 */
SELECT
    s.name,
    o.name,
    st.row_count
FROM    
    sys.objects AS o
        INNER JOIN sys.schemas AS s                 ON s.schema_id  = o.schema_id
        INNER JOIN sys.dm_db_partition_stats st     ON st.object_id = o.object_id

WHERE
    o.type = 'U'                -- User defined is a table.     
    AND st.index_id IN (0, 1)   -- 0 = Heap, 1 = Clustered index.
    AND st.row_count > 500
ORDER BY 
    st.row_count DESC
;

The returned row count is an approximation. Unless you need 100% accuracy it should serve your purposes. I tested the query against a billion record plus database, and found all row counts exactly matched the underlying tables. See the docs for more on this.

Limit SQL query to only scan tables with less than 500 records

3 Answers3