DECLARE @SQL VARCHAR(MAX) SELECT @SQL=ISNULL(@SQL+' UNION ALL ','')+'SELECT '''+T1.NAME+''' AS TB,COUNT(1) AS TOTAL,'+ CASE WHEN T2.NAME IS NOT NULL THEN 'SUM(CASE WHEN STATE=1 THEN 1 ELSE 0 END)' ELSE 'NULL' END+ ' AS STATE,'+ CASE WHEN T3.NAME IS NOT NULL THEN 'SUM(CASE WHEN YEAR(CREATE_TIME)=YEAR(GETDATE()) THEN 1 ELSE 0 END)' ELSE 'NULL' END+ ' AS CREATE_TIME,'+ CASE WHEN T4.NAME IS NOT NULL THEN 'SUM(CASE WHEN YEAR(INPUT_TIME)=YEAR(GETDATE()) THEN 1 ELSE 0 END)' ELSE 'NULL' END+ ' AS INPUT_TIME FROM ['+T1.NAME+']' FROM SYS.TABLES T1 LEFT JOIN ( SELECT OBJECT_NAME(OBJECT_ID) AS NAME FROM SYS.COLUMNS WHERE NAME='STATE' ) T2 ON T1.NAME=T2.NAME LEFT JOIN ( SELECT OBJECT_NAME(OBJECT_ID) AS NAME FROM SYS.COLUMNS WHERE NAME='CREATE_TIME' ) T3 ON T1.NAME=T3.NAME LEFT JOIN ( SELECT OBJECT_NAME(OBJECT_ID) AS NAME FROM SYS.COLUMNS WHERE NAME='INPUT_TIME' ) T4 ON T1.NAME=T4.NAME EXEC (@SQL+' order by tb')