CODEWITHSUNDEEP
sqlsql-server
user3484775
user3484775

Reputation: 29

I need a query which gives all table names and no of records inserted per day

I have 30 tables like a, b, c,..d1 and the columns names are id, name, updated time. The column names vary but updated time column is common for all tables. Can anyone help me regarding the above query?

Upvotes: 2

Views: 94

Answers (2)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

You can dynamically retrieve all the tables with the common specific column (updated_time, I assume this is a DATETIME datatype-column), and show the count of records each date for each table

USE TESTDB --use the schema where your tables are
GO

DECLARE @nSQL NVARCHAR(MAX)

SELECT @nSQL = COALESCE(@nSQL + 'UNION ALL ' + CHAR(10), '') +
               'SELECT ''' + TABLE_NAME + ''' AS TableName, 
                        CAST(updated_time AS DATE) [date],
                        COALESCE(COUNT(*),0) cnt                         
                  FROM ' + QUOTENAME(TABLE_NAME) + CHAR(10) +
               ' GROUP BY CAST(updated_time AS DATE) '
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME = 'updated_time'

EXECUTE(@nSQL);

Sample data

CREATE TABLE t_tab1 (id INT, fname VARCHAR(10), updated_time DATETIME);
CREATE TABLE t_tab2 (id INT, lname VARCHAR(10), updated_time DATETIME);
CREATE TABLE t_tab3 (id INT, mname VARCHAR(10), updated_time DATETIME);
CREATE TABLE t_tab4 (id INT, addr VARCHAR(10), updated_time DATETIME);

INSERT INTO t_tab1 VALUES(1,'aaa',CAST('09/17/2017' AS DATE));
INSERT INTO t_tab1 VALUES(2,'bbb',CAST('09/17/2017' AS DATE));
INSERT INTO t_tab1 VALUES(1,'aaa',CAST('09/18/2017' AS DATE));
INSERT INTO t_tab1 VALUES(2,'bbb',CAST('09/18/2017' AS DATE));
INSERT INTO t_tab1 VALUES(3,'ccc',CAST('09/18/2017' AS DATE));
INSERT INTO t_tab2 VALUES(1,'aaa',CAST('09/18/2017' AS DATE));
INSERT INTO t_tab2 VALUES(2,'bbb',CAST('09/18/2017' AS DATE));
INSERT INTO t_tab3 VALUES(1,'ccc',CAST('09/18/2017' AS DATE));
INSERT INTO t_tab3 VALUES(2,'aaa',CAST('09/18/2017' AS DATE));
INSERT INTO t_tab3 VALUES(3,'bbb',CAST('09/18/2017' AS DATE));
INSERT INTO t_tab3 VALUES(4,'ccc',CAST('09/18/2017' AS DATE));
INSERT INTO t_tab4 VALUES(1,'aaa',CAST('09/18/2017' AS DATE));
INSERT INTO t_tab4 VALUES(2,'bbb',CAST('09/18/2017' AS DATE));

Result

TableName   date        cnt
t_tab1      2017-09-17  2
t_tab1      2017-09-18  3
t_tab2      2017-09-18  2
t_tab3      2017-09-18  4
t_tab4      2017-09-18  2

Ofcourse, you can always edit the query to just show the records you want.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

You could union together all the tables and then do an aggregation on the update time column, e.g.

SELECT
    t.updated,
    COUNT(*) AS cnt
FROM
(
    SELECT updated FROM a
    UNION ALL
    SELECT updated FROM b
    UNION ALL
    SELECT updated FROM b
    UNION ALL
    ...
) t
GROUP BY t.updated;

Upvotes: 0

Related Questions