Solved

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

Posted on 2014-09-10
10
478 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dimension table indexes 8 25
Error Creating Foreign Keys in SQL Database 7 34
SQL Pivot Rows To Columns 10 52
Better way to make a query with date filter. 5 22
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Encryption for Business Encryption (https://en.wikipedia.org/wiki/Encryption) ensures the safety of our data when sending emails. In most cases, to read an encrypted email you must enter a secret key that will enable you to decrypt the email. T…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

813 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

16 Experts available now in Live!

Get 1:1 Help Now