查詢 DB 中所有 table 的資料筆數、列出所有的 table schema

 

查詢 DB 中所有 table 的資料筆數

SELECT S.NAME '結構描述', O.NAME '資料表名稱', P.ROWS '列總數'
FROM SYS.OBJECTS O INNER JOIN SYS.SCHEMAS S ON O.SCHEMA_ID = S.SCHEMA_ID
INNER JOIN SYS.PARTITIONS P ON O.OBJECT_ID = P.OBJECT_ID
WHERE (O.TYPE = 'U') AND (P.INDEX_ID IN (0,1))
ORDER BY P.ROWS DESC;

列出所有的 table schema

SELECT C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME, 
C.IS_NULLABLE, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH,
CASE 
  WHEN U.COLUMN_NAME = C.COLUMN_NAME THEN U.COLUMN_NAME
  ELSE null
END PK
FROM INFORMATION_SCHEMA.COLUMNS C 
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON U.TABLE_SCHEMA = C.TABLE_SCHEMA and U.TABLE_NAME = C.TABLE_NAME