Mehedi's Earth

Recently, I was required to work on a database where my customer renamed tables physically for keeping their historical sales data. Meaning, they renamed the physical table in their own convention like "_SALES_YYYY-MM-DD". Over the period they got many tables. I need to find out a particular sales person's full data. Since they renamed table frequently, so I want to write a future proof persistent query, I took advantage of INFORMATION_SCHEMA Views along with cursor:

DECLARE @tab varchar(100)  
DECLARE @sql varchar(2000)

DECLARE @SalesData table (
    sales_qty     int,
    sales_person    varchar(20)
)

DECLARE SalesPerson CURSOR FOR
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_CATALOG='YOUR_DB_NAME'
        AND TABLE_NAME like 'YOUR_TABLE_NAME%'
    
        
    OPEN SalesPerson
    FETCH NEXT FROM SalesPerson INTO @tab
    WHILE @@fetch_status <> -1
    BEGIN
       IF @@fetch_status = 0
       BEGIN
              
          
    set @sql = '
    INSERT INTO @SalesData(sales_qty,sales_person)
    SELECT  sales_qty,sales_person  FROM [_' +  @tab + ']
    WHERE sales_person =''YOUR_SALES_PERSON'''
    
    exec (@sql)        
        
         
       END
       FETCH NEXT FROM SalesPerson INTO @tab
    END
    DEALLOCATE SalesPerson

SELECT * FROM @SalesData