I can generate queries like this:
select * from table1 where PRO_KEY = '326' (PRO_KEY is numeric data type)
select * from table2 where PRO_KEY = '326'
select * from table3 where PRO_KEY = '326'
select * from table4 where PRO_KEY = '326'
select * from table5 where PRO_KEY = '326'
select * from table170 where PRO_KEY = '326'
The goal is to find what is the total amount of space (in MB/GB) occupied in the database for PRO_KEY 326 in the entire database for the 170 tables.
for example, table1 size is 1.5 GB, and PRO_KEY = '326' is 0.9 GB and PRO_KEY <> '326' is 0.5GB and PRO_KEY = '326' NULL is 0.1 GB.
(we are only interested in the SUM of all the PRO_KEY = '326' data for the 170 tables)
the 170 tables can be derived this way "select * from sys.columns where name ='pro_key'.
right now i can check the total table size by this way:
object_name(so.object_id) AS TableName,
CAST(SUM(ps.reserved_page_count) * 8.0 / (1024.) as Decimal(12,0)) AS Size_MB
sys.dm_db_partition_stats ps JOIN sys.indexes i
ON i.object_id = ps.object_id AND i.index_id = ps.index_id
JOIN sys.objects so
ON i.object_id = so.object_id
WHERE so.type = 'U'
GROUP BY so.object_id
ORDER BY 2 DESC
but does not help me in this quest as there are several pro_key's. The management wants to know how much disk they are going to need if they want to bring another set of pro_key '326' in another environment (these are pretty huge databases)