Solved

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

Posted on 2014-09-10
10
484 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:knowlton
  • 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:knowlton
ID: 40315500
I'll try it...


How do you find out about undocumented sp's like "sp_msforeachdb"
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware ā€“ malware that gets on your customersā€™ computers, encrypts their data, and extorts a hefty ransom for the decryption keys ā€“ is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 5

Author Comment

by: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: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 500 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: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:knowlton
ID: 40315565
Thanks for your fast help and courteous, patient responses!

Tom
0
 
LVL 5

Author Comment

by: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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A ā€¦
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

820 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