Question

我试图通过申请(由他人开发)确定哪些表格受到数据更新的影响。亚洲开发银行有200多个表格,我想避免对每个表格进行改动。

Is there any other way where I can list all table names along with their row count?

select table_name from information_schema.tables

在我的行文中列出所有表格,我如何也包含一些行文?

Answer 1

SELECT sc.name + . + ta.name TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 ORDER BY SUM(pa.rows) DESC

参见:

SQL SERVER – Find Row Count in Table – Find Largest Table in Database.

Answer 2

将所有表格输入数据库:

select * from INFORMATION_SCHEMA.TABLES

a. 将所有栏目输入数据库:

select * from INFORMATION_SCHEMA.columns

a. 收集所有意见。

select * from INFORMATION_SCHEMA.TABLES where table_type =  view

Answer 3

任满。

如果您在<><>查询<>all <>/strong> >上 > >> database 然后,我发现这一组合:,即:和。是否最佳的想法:

SET NOCOUNT ON DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname, RowsCount int ) DECLARE @SQL nvarchar(4000) SET @SQL= SELECT ? AS DbName, s.name AS SchemaName, t.name AS TableName, p.rows AS RowsCount FROM [?].sys.tables t INNER JOIN sys.schemas s ON t.schema_id=s.schema_id INNER JOIN [?].sys.partitions p ON p.OBJECT_ID = t.OBJECT_ID INSERT INTO @AllTables (DbName, SchemaName, TableName, RowsCount) EXEC sp_msforeachdb @SQL SET NOCOUNT OFF SELECT DbName, SchemaName, TableName, SUM(RowsCount), MIN(RowsCount), SUM(1) FROM @AllTables WHERE RowsCount > 0 GROUP BY DbName, SchemaName, TableName ORDER BY DbName, SchemaName, TableName

Answer 4

简单而快捷

SELECT T.name AS [TABLE NAME], I.rows AS [ROWCOUNT] 
FROM   sys.tables AS T 
   INNER JOIN sys.sysindexes AS I ON T.object_id = I.id 
   AND I.indid < 2 ORDER  BY I.rows DESC

Answer 5

将每个表格的浏览量输入数据库。也可以通过改变SCHEMA_NAME来过滤。

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) +  .  + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]

FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type =  U 
      AND SCHEMA_NAME(sOBJ.schema_id) =  dbo 
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY SUM(sPTN.Rows),[TableName]

Is there any other way where I can list all table names along with their row count?

友情链接