AronChan AronChan - 29 days ago 9
SQL Question

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

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?

Answer

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?