Thought a while about your problem and here's the solution I think you search for:
1) Create a Stored Procedure like
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetTableInfos
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Schema nvarchar(200);
DECLARE @Table nvarchar(200);
DECLARE @Column nvarchar(200);
DECLARE @Cmd nvarchar(4000);
DECLARE @TableInfos TABLE
(
[SchemaName] nvarchar(200)
,[TableName] nvarchar(200)
,[RowCount] int
,[MinAge] int
,[MaxAge] int
)
DECLARE curLoop CURSOR LOCAL FAST_FORWARD FOR
SELECT [TABLE_SCHEMA]
, [TABLE_NAME]
, [COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE ([COLUMN_NAME] = 'Age')
AND ([DATA_TYPE] = 'int');
OPEN curLoop;
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM curLoop INTO @Schema, @Table, @Column;
IF (@@FETCH_STATUS <> 0)
BREAK;
SET @Cmd = 'SELECT ''' + @Schema + '''
, ''' + @Table + '''
, (SELECT COUNT(*) FROM [' + @Schema + '].[' + @Table + '])
, (SELECT MIN([' + @Column + ']) FROM [' + @Schema + '].[' + @Table + '] WHERE ([' + @Column + '] IS NOT NULL))
, (SELECT MAX([' + @Column + ']) FROM [' + @Schema + '].[' + @Table + '] WHERE ([' + @Column + '] IS NOT NULL))'
INSERT INTO @TableInfos
(
[SchemaName]
,[TableName]
,[RowCount]
,[MinAge]
,[MaxAge]
)
EXEC sp_executesql @Cmd;
END
CLOSE curLoop;
DEALLOCATE curLoop;
SELECT * FROM @TableInfos;
END
GO
You may alter the name of the column that contains the ages to aggregate or add more info columns to the result set.
2) Change your SELECT statement to get the table infos into
EXECUTE [dbo].[GetTableInfos]
This will return the result set from the stored procedure.
Hope this will fit your needs?
Good luck!