-1

We have a DB which has several schemas and each schema has a Fact table. I need to prepare a result set with schema name, max(MTH_DT) from Fact and distinct MTH_DT counts from each Fact table.

SCHEMA_NAME  MAX(MTH_DT)    DISTINCT_COUNT(MTH_DT)
SCHM_1       11/30/2015     24
SCHM_2       10/31/2015     24
SCHM_3       11/30/2015     36
SCHM_4       10/31/2015     24
SCHM_5       11/30/2015     24

How can I get the resultset in this fashion?

Triumph Spitfire
  • 663
  • 15
  • 38
  • Could you post some sample data and how data looks like – TheGameiswar Jan 20 '16 at 17:29
  • There is one Fact table for each schema. Each Fact table has a column called MTH_DT which holds dates. MTH_DT has duplicate entries. I need to get the max(MTH_DT) from each fact table. Also the distinct MTH_DT from it. So, as shown above, for SCHM_1, the max date is 11/30/2015 and the distinct count for dates is 24. The data is huge for me to post here. Please let me know if you need more details. – Triumph Spitfire Jan 20 '16 at 17:46
  • Does this need to be dynamic based on the current schemas or do you know ahead of time the schemas involved? – Sean Lange Jan 20 '16 at 17:57
  • This should be dynamic as we have new schemas added each month (i.e, new Facts gets added). I – Triumph Spitfire Jan 20 '16 at 18:00
  • Ugh that stinks. You will have to leverage sys.schemas and build some dynamic sql. Do you also have some schemas that don't have a Facts table? – Sean Lange Jan 20 '16 at 18:00
  • I understand it is not straight forward. But all schemas will have a Fact table and the Fact table structure is constant through out. – Triumph Spitfire Jan 20 '16 at 18:02
  • Not all schemas will have that table. It might be in dbo but it is NOT going to be in guest, INFORMATION_SCHEMA, sys or any of the default db_ schemas. You could look at sys.schemas to build some dynamic sql for this. Where schema_id > 4 and schema_id < 16384. Once you have the list of schemas this is pretty simple dynamic sql. – Sean Lange Jan 20 '16 at 18:10

2 Answers2

0

Here it goes

Just uncomment the EXECUTE statement when you are sure of the query and run the query:

DECLARE @SQL VARCHAR(MAX) = ''

SELECT @SQL += 'SELECT '''+T.TABLE_NAME+''' AS [SCHEMA_NAME],MAX(MTH_DT) AS [MAX(MTH_DT)] ,COUNT(MTH_DT)  AS [DISTINCT_COUNT(MTH_DT)] FROM '+T.TABLE_SCHEMA+'.'+T.TABLE_NAME + ' UNION ' FROM INFORMATION_SCHEMA.TABLES AS T WHERE T.TABLE_NAME  like 'SCHM_%'
SELECT  @SQL = SUBSTRING(@SQL,1,LEN(@SQL) - LEN('UNION'))

PRINT (@SQL)
--EXECUTE (@SQL)
Fuzzy
  • 3,810
  • 2
  • 15
  • 33
  • I only worked with the information I had @TriumphSpitfire could you please let me know the table definitions so I can better construct the dynamic SQL – Fuzzy Jan 20 '16 at 18:20
  • You also should NOT use this catalog to determine the schema of a table. https://msdn.microsoft.com/en-us/library/ms186224.aspx – Sean Lange Jan 20 '16 at 18:23
  • I'm posting some potion of table sctructure here. CREATE TABLE [SCHM_1].[FACT] ( [ID] bigint NULL, [IDB_ID] bigint NULL, [MTH_DT] datetime NULL, – Triumph Spitfire Jan 20 '16 at 18:30
0

Here is a safer way to build your dynamic sql than using INFORMATION_SCHEMA.TABLES. I left off the group by because it is a constant so you don't actually need a group by.

declare @SQL nvarchar(MAX) = ''

select @SQL = @SQL + 'select ''' + name + ''' as SchemaName, MAX(MTH_DT) as MaxMTH_DT, COUNT(distinct MTH_DT) as DISTINCT_COUNT_MTH_DT from ' + name + '.Fact union all ' 
from sys.schemas
where SCHEMA_ID > 4
    and SCHEMA_ID < 16384

Select @SQL = LEFT(@SQL, LEN(@SQL) - 9) + ' order by SchemaName'

select @SQL
Sean Lange
  • 33,028
  • 3
  • 25
  • 40