如何使用sys.tables选择表

本文介绍了如何使用sys.tables选择表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 大家好，我将使用以下代码选择一些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 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 注意。 我不想要rowCount，10个表中的每一个都有一个数量字段。我想显示另一个col中每个表的金额总和。 谢谢。解决方案 首先，你的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.nameFROM sysobjects sobjectsWHERE sobjects.xtype = 'U' and SUBSTRING(sobjects.name,0,10) = 'MyFirstTenCharacter'namer_Sales05r_Sales05_CombinedRequestsr_Sales05_NotOrderedr_Sales05_NotRequestedr_Sales05_SplitRequestsr_Sales05_UnmatchedAmountsr_Sales05_UnmatchedCustomersr_Sales05_UnmatchedItemNumbersr_Sales05_UnmatchedQuantitiesr_Sales05_UnmatchedYearsIt 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 Amountr_Sales05 0r_Sales05_CombinedRequests 100r_Sales05_NotOrdered 1000r_Sales05_NotRequested 900000r_Sales05_SplitRequests 3r_Sales05_UnmatchedAmounts 5r_Sales05_UnmatchedCustomers 0r_Sales05_UnmatchedItemNumbers 12r_Sales05_UnmatchedQuantities 100000r_Sales05_UnmatchedYears 0Note. 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_countFROM sysobjects sobjectsJOIN sys.dm_db_partition_stats st ON OBJECT_NAME(OBJECT_ID) = sobjects.nameWHERE sobjects.xtype = 'U' and SUBSTRING(sobjects.name,1,10) = 'MyFirstTenCharacter' 这篇关于如何使用sys.tables选择表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！