Ok. I still think your set up is strange and we're struggling to understand what you are trying to do, however here is some stuff you can alter to your own requirements.
I'm creating a interim results table:
if exists (select * from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'tempResults' AND TABLE_SCHEMA = 'dbo') drop table dbo.tempResults
create table tempResults
(
id int identity(1,1),
databasename varchar(128),
schemaname varchar(128),
tablename varchar(128),
columnname varchar(1128),
rowsintable int,
system_type_id int,
minvalue varchar(128),
maxvalue varchar(128)
)
Note the
minvalue and
maxvalue columns cannot be determined from the system tables (which is part of the problem with your approach)
I've populated that table initially with every column of every table of every database on my server :
DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @sql = N'', @src = N' UNION ALL
SELECT ''$d'' as databasename,
s.name COLLATE SQL_Latin1_General_CP1_CI_AI as schemaname,
t.name COLLATE SQL_Latin1_General_CP1_CI_AI as tablename,
c.name COLLATE SQL_Latin1_General_CP1_CI_AI as columnname,
i.rowcnt as rowsintable, c.system_type_id as system_type_id,
cast(0 as varchar(128)) as minvalue, cast(0 as varchar(128)) as maxvalue
FROM [$d].sys.schemas AS s
INNER JOIN [$d].sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN [$d].sys.all_columns as c ON c.object_id=t.object_id
INNER JOIN [$d].sys.sysindexes i ON c.object_id = i.id
';
SELECT @sql = @sql + REPLACE(@src, '$d', name)
FROM sys.databases
WHERE database_id > 4
AND [state] = 0
AND HAS_DBACCESS(name) = 1;
SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));
SET @sql = 'INSERT INTO tempresults SELECT * FROM (' + @sql + ') AS C '
EXEC sys.sp_executesql @sql;You might prefer to (i.e. should) limit this to the table naming convention that you are using for your user tables OR the column name(s) that contain the data you want.
I then update this table by querying the actual tables on the databases themselves:
declare @dbname varchar(max)
declare @scheme varchar(max)
declare @table varchar(max)
declare @column varchar(max)
declare @ccSql nvarchar(max)
declare @maxid int =(SELECT MAX(id) from tempresults)
declare @curid int = (SELECT MIN(id) from tempresults)
WHILE @curid <= @maxid
BEGIN
SELECT @dbname = databasename,
@scheme = schemaname,
@table = tablename,
@column = columnname
FROM tempresults TD
INNER JOIN sys.types T on TD.system_type_id = T.system_type_id
WHERE id = @curid
AND T.name NOT IN ('bit','image','text','ntext')
if @column IS NOT NULL
BEGIN
SET @ccSql = 'UPDATE tempresults SET minvalue = B.the_min, maxvalue=B.the_max FROM ('
SET @ccSql = @ccSql + 'SELECT MIN(['+@column+']) AS the_min, MAX([' + @column + ']) AS the_max FROM ['
SET @ccSql = @ccSql + @dbname + '].[' + @scheme + '].[' +@table +']) B WHERE id=' + cast(@curid as varchar(128))
EXEC sys.sp_executesql @ccsql;
END
SET @curid = @curid + 1
ENDPoints to consider:
1. You shouldn't need to be doing this - there is clearly something wrong with your design.
2. This takes a long time to run, so try to filter it further
3. You might like to limit the columns examined even further to just include date or datetime or integers (I have no idea where you are getting Year1 and Year2 from) - this would improve the performance of the query.
4. You may or may not get some warnings about NULL values in aggregate functions (I ignored these).
I get results similar to the following:
Northwind dbo Employees EmployeeID 9 56 1 9
Northwind dbo Employees LastName 9 231 Buchanan Suyama
Northwind dbo Employees FirstName 9 231 Andrew Steven
Northwind dbo Employees TitleOfCourtesy 9 231 Dr. Ms.
Northwind dbo Employees BirthDate 9 61 Sep 19 1937 12:00AM Jan 27 1966 12:00AM
Northwind dbo Employees HireDate 9 61 Apr 1 1992 12:00AM Nov 15 1994 12:00AM
Northwind dbo Employees Address 9 231 14 Garrett Hill Edgeham Hollow
Winchester Way
Submit an article or tip