Untested, but something like this should work

BEGIN TRAN

DECLARE @SQL AS VARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL,'') + 'SELECT ' + c.name + ', '''+c.name+''' AS columnName FROM ' + o.name + ' WHERE LEN('+c.name+')=9;' + CHAR(10)

FROM sys.objects o

INNER JOIN sys.columns c ON o.object_id = c.object_id

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE o.type = 'U' AND

t.name IN ('tinyint','smallint','int','real','money','float','decimal','numeric','smallmoney','bigint')

EXEC (@SQL)

ROLLBACK