Question: So I have a database with many tables that have a column that contains a GL Account value (for financial purposes). The column name varies by table (i.e. in one table the column is called "gldebitaccount" and in another table it's called "glcreditaccount"). I was able to find all combinations of table / column pairs using the following query:

```
SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%gl%acc%'

```

This query returns close to 100 pairs of tables/columns. I am trying to find any value in any of those table/column pairs that exceeds 25 chars in length. For an individual table/column, I'd typically use:

```
SELECT *
FROM tableName
WHERE LEN(columnName)>25

```

I want to avoid having to run that query 100 times with each pair. Is there any way I can do a "for each" (which I know is frowned upon in SQL since everything should be set-based). I've done sub-SELECT statements before, but not any that involved change the table in the FROM clause. Any ideas or help would be greatly appreciated!

Thanks in advance!

Answer: 
Here's an option without cursors that also doesn't add XML overhead. Note that it also protects you from potential type conflicts (e.g. try the others in a database with hierarchyid columns, like AdventureWorks), from table or column names with apostrophes, and from table names that exist in more than one schema.

```
DECLARE @sql NVARCHAR(MAX) = N'';

CREATE TABLE #Results
(
  SchemaName NVARCHAR(128), TableName  NVARCHAR(128), ColumnName NVARCHAR(128)
);

SELECT @sql += N'INSERT #Results SELECT ''' 
    + REPLACE(s.name,'''','''''') + ''',''' 
    + REPLACE(t.name,'''','''''') + ''',''' 
    + REPLACE(c.name,'''','''''') + '''
  WHERE EXISTS (SELECT 1 FROM ' + QUOTENAME(s.name) 
   + '.' + QUOTENAME(t.name) + ' WHERE 
   LEN(' + QUOTENAME(c.name) + ') > 25);
 '
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE 
(
  c.system_type_id IN (35,99) -- text,ntext
  OR (c.system_type_id IN (167,231) -- varchar,nvarchar, could be max
      AND c.max_length > 25 OR c.max_length = -1)
  OR (c.system_type_id IN (175,239) -- char, nchar
      AND c.max_length > 25)
)
AND c.name LIKE N'%gl%acc%';

EXEC sp_executesql @sql;

SELECT SchemaName, TableName, ColumnName FROM #Results;

```
You will need to create dynamic SQL because you cannot dynamically specify the source table. You could do this using a cursor, or write a select statement that makes a row for each statement you need to run. This shows how to do it with a cursor. You problem looks like an acceptable usage for a cursor:

```
DECLARE @ColName VARCHAR(MAX);
DECLARE @TableName VARCHAR(MAX);
DECLARE @SomeSQL VARCHAR(MAX);

DECLARE db_cursor CURSOR FOR  
    SELECT c.name AS ColName, t.name AS TableName
    FROM sys.columns c
        JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE '%gl%acc%'

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @ColName, @TableName;

WHILE @@FETCH_STATUS = 0  
BEGIN  

    -- you need to make dynamic SQL
    SELECT @SomeSQL = 'SELECT * FROM ' + @TableName + ' WHERE LEN(' + @ColName + ') > 25;'
    PRINT(@SomeSQL + CHAR(10));

    -- you could execute it directly if you wish.
    --EXEC (@SomeSQL);

    FETCH NEXT FROM db_cursor INTO @ColName, @TableName;

END  

CLOSE db_cursor;
DEALLOCATE db_cursor;

```