• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

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

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
Tom Knowlton
Asked:
Tom Knowlton
  • 6
  • 4
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
Use the undocumented procedure sp_msforeachdb

exec sp_msforeachdb 'use [?]; select top 10 * from foo order by column'
0
 
Steve WalesSenior Database AdministratorCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
I'll try it...


How do you find out about undocumented sp's like "sp_msforeachdb"
0
Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

 
Tom KnowltonWeb developerAuthor Commented:
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
 
Tom KnowltonWeb developerAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
Sorry, I have to object - you shouldn't accept your own answer to the question, when I provided the answer for you.
0
 
Steve WalesSenior Database AdministratorCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
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
 
Tom KnowltonWeb developerAuthor Commented:
Thanks for your fast help and courteous, patient responses!

Tom
0
 
Tom KnowltonWeb developerAuthor Commented:
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

Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now