问题描述
我正在使用 sp_MSforeachtable 来获取数据库中特定表的行数.我想要这些按名称排序.
I am using sp_MSforeachtable to get a rowcount of specific tables in my database. I want these ordered by name.
如何向 sp_MSforeachtable 添加 ORDER BY 子句?
How do I add an ORDER BY clause to sp_MSforeachtable?
推荐答案
我知道这个问题已经有 10 多年的历史了,但它有超过 3000 次访问和一堆错误答案.我将重新调整 Chris R. 的答案,希望将其标记为已接受的答案,而不是过于复杂的半页 SQL 或你不能"的答案.我带着完全相同的问题来到这里,所以它仍然是相关的,显然并不简单.
I understand this question is over 10 years old, but it has over 3000 visits and a bunch of wrong answers. I'm going to repurpose Chris R.'s answer in hopes of getting this marked as the accepted answer, instead of overly-complicated half-pages of SQL or "you can't" answers. I came here with the exact same question so it's still relevant and obviously not simple.
使用@whereand参数指定一个ORDER BY子句,该参数的内容被附加到内部SELECT的末尾> 通过存储过程中的简单 + @whereand 语句.而在 ORDER BY 1 中使用 1 表示按第一列排序.
Use the @whereand parameter to specify an ORDER BY clause, The contents of that parameter are tacked on to the end of the internal SELECT statement via a simple + @whereand in the stored proc. And using 1 in ORDER BY 1 means to order by the first column.
sp_MSforeachtable @command1='SELECT COUNT(*) AS ''?'' FROM ?', @whereand = 'ORDER BY 1'