Solved

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

Posted on 2014-09-10
10
466 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 48
table fragmentation 40 74
Sql server lock cursor 13 41
optimize  c# code 7 49
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 …
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.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now