?
Solved

Loop through all databases, select top 10 rows from table "foo"

Posted on 2014-09-10
10
Medium Priority
?
507 Views
Last Modified: 2014-09-10
Is it possible to loop through all databases on a SQL Server, select top 10 rows from table "foo" (assuming every database has this table)?

Failing that, if I could get a count of records that would atleast tell me what database to look in.  The database I am working in has the table, but no records.  Maybe one of the other databases has the same table with some records in it.

loop
0
Comment
Question by:Tom Knowlton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40315445
Use the undocumented procedure sp_msforeachdb

exec sp_msforeachdb 'use [?]; select top 10 * from foo order by column'
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40315455
You could even modify that a little to check for the existence of the table:

exec sp_msforeachdb 'use [?]; if exists (select * from sys.tables where name = ''foo'') select top 10 * from foo order by column'

Taking it just a little further still, if you want to add the database name to the last column of each output set, this should work too (so you know what database each set comes from).

exec sp_msforeachdb 'use [?]; if exists (select * from sys.tables where name = ''foo'') select top 10 *, ''?'' from foo order by column'
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 40315500
I'll try it...


How do you find out about undocumented sp's like "sp_msforeachdb"
0
Threat Trends for MSPs to Watch

See the findings.
Despite its humble beginnings, phishing has come a long way since those first crudely constructed emails. Today, phishing sites can appear and disappear in the length of a coffee break, and it takes more than a little know-how to keep your clients secure.

 
LVL 5

Author Comment

by:Tom Knowlton
ID: 40315515
This is working:

exec sp_msforeachdb 'use [?]; if exists (select * from sys.tables where name = ''DBFiles'') select top 10 *, ''?'' from DBFiles order by DBFileID'





How do I modify it to look like this:

DatabaseName, TableName, RecordCount
Foo, Bar, 10

Only show entries where RecordCount > 0
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 40315542
I've requested that this question be closed as follows:

Accepted answer: 0 points for knowlton's comment #a40315515

for the following reason:

Nice work.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40315543
Sorry, I have to object - you shouldn't accept your own answer to the question, when I provided the answer for you.
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 2000 total points
ID: 40315548
In any event in answer to your follow up questions:

1) Google "SQL Server Undocumented Stored Procedures"

2) Try this:
exec sp_msforeachdb 'use [?]; if exists (select * from sys.tables where name = ''foo'') if exists (select * from foo) select ''?'', ''foo'', count(*) from foo'
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 40315561
I did not mean to accept my own answer.  So sorry!


Here was my attempt, before you posted your answer just now.

exec sp_msforeachdb 'use [?]; if exists (select * from sys.tables s where name = ''DBFiles'') select ''?'' as DatabaseName, ''DBFiles'' as TableName, COUNT(*) as RecordCount from DBFiles'

Let me try yours!
0
 
LVL 5

Author Closing Comment

by:Tom Knowlton
ID: 40315565
Thanks for your fast help and courteous, patient responses!

Tom
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 40315569
Perfect!

exec sp_msforeachdb 'use [?]; if exists (select * from sys.tables where name = ''DBFiles'') if exists (select * from DBFiles) select ''?'' as DatabaseName, ''DBFiles'' as TableName, count(*) as RecordCount from DBFiles'

Yields:

final results
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ransomware continues to be a growing problem for both personal and business users alike and Antivirus companies are still struggling to find a reliable way to protect you from this dangerous threat.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question