Sep 21, 2017

How to get all of tables record in AX 2012 by SQL Server(MS SQL, T-SQL) (MS SQL에서 AX 모든 테이블 레코드 찾기)

--TOTAL TABLE'S RECORD
SELECT SUM(TABLECOUNT.RECORDCNT)
FROM (SELECT DISTINCT OBJ.NAME AS TABLENAME,
  CNT.ROWS AS RECORDCNT
 FROM   SYSINDEXES CNT
  INNER JOIN SYS.TABLES OBJ
  ON CNT.ID = OBJ.OBJECT_ID
 WHERE  OBJ.IS_MS_SHIPPED = 0
  AND OBJ.TYPE = 'U'
  AND OBJ.SCHEMA_ID != 6) AS TABLECOUNT

--RECORDS BY TABLE
SELECT DISTINCT OBJ.NAME AS TABLENAME,
  CNT.ROWS AS RECORDCNT
FROM   SYSINDEXES CNT
INNER JOIN SYS.TABLES OBJ
  ON CNT.ID = OBJ.OBJECT_ID
WHERE  OBJ.IS_MS_SHIPPED = 0
AND OBJ.TYPE = 'U'
AND OBJ.SCHEMA_ID != 6
ORDER BY OBJ.NAME

No comments:

Post a Comment