我们怎样才能在所有表的数据库中获得明年的总行数？

I have a requirement to get the year wise tables total row count from a database. For example , if I have a database 'Adventures2008' which has 100 tables.In which only few tables had a column '[ModifiedTimeStamp]'. Now I want to get the dataset as below format

我需要从数据库中获取年度明智的表总行数。例如,如果我有一个数据库'Adventures2008',它有100个表。其中只有少数表有一个列'[ModifiedTimeStamp]'。现在我想以下面的格式获取数据集

The column '[ModifiedTimeStamp]' is not present in few of the tables. So I need a SQL query to get the required output.I have tried many ways to but i'm unable to loop the tables with column 'ModifiedTimeStamp'.

少数几个表中没有“[ModifiedTimeStamp]”列。所以我需要一个SQL查询来获取所需的输出。我已经尝试了很多方法,但我无法使用'ModifiedTimeStamp'列循环表。

Could anyone help me on this ?

有人可以帮我吗?

Please let me know if any concerns/questions.

如有任何疑问/问题,请告诉我。

1 个解决方案

#1

If I'm not wrong,

如果我没错,

What exactly You need is row count of each table containing column "ModifiedTimeStamp" right?

您需要的是包含“ModifiedTimeStamp”列的每个表的行数对吗?

try this query...

试试这个查询......

-------------- find out tables containing column "MODIFIEDTIMESTAMP"-------------------
DROP TABLE #TMP1
SELECT 
    ROW_NUMBER() OVER(ORDER BY TABLE_NAME) ID, 
    TABLE_NAME 
INTO 
    #TMP1
FROM 
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    COLUMN_NAME LIKE '%MODIFIEDTIMESTAMP%' 
    ORDER BY TABLE_NAME
-----------------------------------------------------------------------------

------------------loop each tables found, -------------------------------------
DECLARE @CNT INT = 1  ;
DECLARE @RN INT = (SELECT COUNT(1) FROM #TMP1)

IF @RN >1 
BEGIN

DECLARE @SQL NVARCHAR(MAX)='
IF OBJECT_ID(''TEMPDB.DBO.##TMP2'', ''U'') IS NOT NULL DROP TABLE ##TMP2
SELECT * INTO ##TMP2 FROM ('
WHILE @CNT <= @RN
BEGIN
    DECLARE @TABLE_NAME NVARCHAR(MAX) = (SELECT TABLE_NAME FROM #TMP1 WHERE ID=@CNT)
    SET @SQL = @SQL+ 'SELECT '''+ ISNULL(@TABLE_NAME,'') + ''' AS TABLE_NAME, YEAR(MODIFIEDTIMESTAMP) YEAR,COUNT(1) TOTAL_ROW FROM '+ ISNULL(@TABLE_NAME,'') +' WHERE MODIFIEDTIMESTAMP IS NOT NULL GROUP BY YEAR(MODIFIEDTIMESTAMP) 
UNION ALL
'
   SET @CNT = @CNT + 1;
END
SET @SQL = LEFT(@SQL,LEN(@SQL)-11) + ') Z'
PRINT @SQL
EXECUTE (@SQL)

SELECT * FROM ##TMP2
PIVOT(
    MAX(TOTAL_ROW)
    FOR YEAR IN([2016],[2017]) ---------you can make it dynamic by your self
)X

END

CMIIW, Let me know if this useful... Thanks

CMIIW,让我知道这是否有用......谢谢

#1