如何使用sys.tables选择表 [英] How to select table using sys.tables

问题描述

大家好，我将使用以下代码选择一些tablesNames。

  SELECT  sobjects.name 
  FROM  sysobjects sobjects 
  WHERE  sobjects.xtype = '  U' 和 SUBSTRING（sobjects.name， 0 ， 10 ）= '  MyFirstTenCharacter' 

 name 
 r_Sales05 
 r_Sales05_CombinedRequests 
 r_Sales05_NotOrdered 
 r_Sales05_NotRequested 
 r_Sales05_SplitRequests 
 r_Sales05_UnmatchedAmounts 
 r_Sales05_UnmatchedCustomers 
 r_Sales05_UnmatchedItemNumbers 
 r_Sales05_UnmatchedQuantities 
 r_Sales05_UnmatchedYears 

它将显示11个TableNames。现在我想通过连接选择那些桌子前面的Count（Amount）。像下面的东西。

名称金额
 r_Sales05  0  
 r_Sales05_CombinedRequests  100  
 r_Sales05_NotOrdered  1000  
 r_Sales05_NotRequested < span class =code-digit> 900000  
 r_Sales05_SplitRequests  3  
 r_Sales05_UnmatchedAmounts  5  
 r_Sales05_UnmatchedCustomers  0  
 r_Sales05_UnmatchedItemNumbers  12  
 r_Sales05_UnmatchedQuantities < span class =code-digit> 100000  
 r_Sales05_UnmatchedYears  0  

注意。

我不想要rowCount，10个表中的每一个都有一个数量字段。我想显示另一个col中每个表的金额总和。



谢谢。

解决方案

< blockquote>首先，你的SUBSTRING需要 1,10 ，而不是0,10 - SQL索引从1开始。

  SELECT  sobjects.name，st.row_count 
  FROM  sysobjects sobjects 
  JOIN  sys.dm_db_partition_stats st  ON   OBJECT_NAME （ OBJECT_ID ）= sobjects.name 
  WHERE  sobjects.xtype = '  U' 和 SUBSTRING（sobjects） .name， 1 ， 10 ）= '  MyFirstTenCharacter' 

Hi all , I will select some tablesNames with the below code .

SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U' and SUBSTRING(sobjects.name,0,10) = 'MyFirstTenCharacter' 

name
r_Sales05
r_Sales05_CombinedRequests
r_Sales05_NotOrdered
r_Sales05_NotRequested
r_Sales05_SplitRequests
r_Sales05_UnmatchedAmounts
r_Sales05_UnmatchedCustomers
r_Sales05_UnmatchedItemNumbers
r_Sales05_UnmatchedQuantities
r_Sales05_UnmatchedYears

It will show me 11 TableNames . now i would like to select Count(Amount) of those table's in front of them with a join . Something like below .

name                                              Amount
r_Sales05                                         0
r_Sales05_CombinedRequests                        100  
r_Sales05_NotOrdered                              1000
r_Sales05_NotRequested                            900000
r_Sales05_SplitRequests                           3
r_Sales05_UnmatchedAmounts                        5
r_Sales05_UnmatchedCustomers                      0
r_Sales05_UnmatchedItemNumbers                    12
r_Sales05_UnmatchedQuantities                     100000 
r_Sales05_UnmatchedYears                          0

Note.
I don't want rowCount , each of 10 tables has an amount fields . I would like to show the sum of amounts of each table in the other col .

Thanks .

解决方案

First, you need 1,10in your SUBSTRING, not 0,10 - SQL indexes start at 1.

SELECT sobjects.name, st.row_count
FROM sysobjects sobjects
JOIN sys.dm_db_partition_stats st ON OBJECT_NAME(OBJECT_ID) = sobjects.name
WHERE sobjects.xtype = 'U' and SUBSTRING(sobjects.name,1,10) = 'MyFirstTenCharacter'

这篇关于如何使用sys.tables选择表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！