27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT COUNT(1) FROM A
SELECT COUNT(1) FROM B
SELECT COUNT(1) FROM C
SELECT 'A' AS TB,COUNT(1) AS TOTAL FROM A UNION ALL
SELECT 'B',COUNT(1) FROM B UNION ALL
SELECT 'C',COUNT(1) FROM C
SELECT 'UNION ALL SELECT '''+NAME+''' AS TB,COUNT(1) AS TOTAL FROM ['+NAME+']'
FROM SYS.TABLES
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL=ISNULL(@SQL+'
UNION ALL ','')+'SELECT '''+NAME+''' AS TB,COUNT(1) AS TOTAL FROM ['+NAME+']'
FROM SYS.TABLES
EXEC (@SQL)
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)
select OBJECT_NAME(id) as tablename,MAX(rowcnt) as rownum from sys.sysindexes
where OBJECT_NAME(id) in ('test','tb')
group by id