我有10000个表我想检查表有数据如何检查所有表 [英] I Have 10000 Tables I Want Check The Table Is Having The Data How To Check All The Tables

问题描述

看到我们有大约10000张桌子。对于一个表，我们可以从select * from table中获取数据，如果有10000个表，那么如何检查它

解决方案

对不起，但你的问题没有意义。



由于几个原因，无需从万张表中获取数据。最重要的是：

1）他们的结构可能不同，

2）当你试图获取大量数据时，应用程序可以挂起（留在无响应模式）。



重新考虑数据库设计！

你需要编写一个程序来检查这个。据我所知，没有任何魔法代码可以找到它。您可以按照以下步骤操作。 （我希望你使用的是SQL Server）



使用

查找数据库中的所有表格

 SELECT * FROM information_schema .tables 

现在编写一个while循环并逐个检查所有表中的数据可能会获取前1行或计数（ *）。

除了解决方案1，您可以将该表与分区表连接，以获得单个查询中每个表的行数

  SELECT  * 
  FROM （
  SELECT  
 TableName = t.TABLE_SCHEMA + ' 。 + t.TABLE_NAME 
，[ RowCount ] = SUM（sp。[ROWS]）
  FROM  INFORMATION_SCHEMA.TABLES t 
  JOIN  sys.partitions sp  ON  sp.object_id =  OBJECT_ID （t.TABLE_SCHEMA + ' 。' + t.TABLE_NAME）
  WHERE  TABLE_TYPE = '   BASE TABLE' 
  GROUP   BY  t.TABLE_SCHEMA + ' 。' + t.TABLE_NAME 
）A 
  ORDER   BY  TableName 

此解决方案改编自此帖子在sqlserverplanet [ ^ ]



如果您决定使用 CURSOR [ ^ ]然后不使用 SELECT * 。



您只想知道是否存在任何记录，而不是查看所有数据，所以请使用像

 SELECT TOP 1 FROM [tablename] 

或

 SELECT COUNT（*）FROM [tablename] 

See we have around 10000 tables are there . For one table we can the data from "select * from table " if there are 10000 table then how to check it

解决方案

Sorry, but your question has non-sense.

There is no need to get data from 10 thousand tables, because of several reasons. The most important are:
1) their structure could be differ,
2) application can hang-on when you're trying to get a lots of data (stays in non-response mode).

Re-think the database design!

You need to write a program to check this. As far as I know there isn't any magic code to find this. You can follow these steps. (I hope you are using SQL Server)

Find all table in a database using

SELECT * FROM information_schema.tables

Now write a while loop and check all the tables for the data one by one might be fetching Top 1 rows or count(*).

Further to solution 1 you can join that table with the partitions table to get the row count for each table in a single query

SELECT *
FROM (
    SELECT
        TableName   = t.TABLE_SCHEMA + '.' + t.TABLE_NAME
        ,[RowCount] = SUM(sp.[ROWS])
        FROM INFORMATION_SCHEMA.TABLES t
        JOIN sys.partitions sp ON sp.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
    WHERE TABLE_TYPE = 'BASE TABLE'
    GROUP BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME
) A
ORDER BY TableName

This solution was adapted from this post at sqlserverplanet[^]

If you decide to loop through the tables using a CURSOR[^] then don't use SELECT *.

You only want to know if any records exist, not view all of the data so use something like

SELECT TOP 1 FROM [tablename]

or

SELECT COUNT(*) FROM [tablename]

这篇关于我有10000个表我想检查表有数据如何检查所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！