SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Multiple Databases


Query Multiple Databases

Author
Message
jayoub
jayoub
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1415 Visits: 587
04/04/2013
I am a newbe database admin and I have been tasked to run queries against multiple databases. Each server has an instance and each instance has 10 or more databases. I need to know if there is an easy way to run a query on all the databases at once.

The way I do it now is by adding the database name each time. For example, the beginning of the query you have

USE [database name] ….

I type in the database name every time and execute and grab the results. This method was ok when the instance had only 1-2 production databases but now I have instances that have 10 or more databases plus the system databases. .

I tried using Registered Servers but it was not running against all the databases

Your help is appreciated.

Thanks,
Jeff

Jeff
SQL Show
SQL Show
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1587 Visits: 1078
Google "sp_msforeachdb".
jayoub
jayoub
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1415 Visits: 587
Will do

Thanks.

Jeff
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65492 Visits: 17980
You can also use sys.databases to build a string for you.

For example if you wanted to select * from MyTable on every database you could do something like this.


select 'select * from ' + name + '.MyTable;'
from sys.databases



This is certainly easier than using sp_msforeachdb. You can even use this table to build up a string to execute as dynamic sql if you want.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Database admin(DBA)
Database admin(DBA)
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1504 Visits: 1482
Hi,

please follow the link below for more information.

http://www.codeproject.com/Articles/459536/SQL-Server-Applying-Filter-on-sp_MSforeachDB


u can use sys.databases also.

Thanks&Regards,

SQL server DBA
jayoub
jayoub
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1415 Visits: 587
Thanks, but I may need an example to learn how to do this.

This is a typical query that I would have to run on each database in the instance

use [msdb]
select user_name(o.schema_id) as [shema], o.name as [object],
o.type_desc as [type],
o.create_date as [create date]
from sys.all_objects o, sys.sql_modules s
where o.object_id = s.object_id
and s.definition is not null
and o.is_ms_shipped = 0
order by user_name(o.schema_id), o.name

How would I use the sp_msforeachdb to execute this. Also how will the resultes come out when you have several databases.

Your help is appriciated

Jeff
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65492 Visits: 17980
jayoub1 (4/8/2013)
Thanks, but I may need an example to learn how to do this.

This is a typical query that I would have to run on each database in the instance

use [msdb]
select user_name(o.schema_id) as [shema], o.name as [object],
o.type_desc as [type],
o.create_date as [create date]
from sys.all_objects o, sys.sql_modules s
where o.object_id = s.object_id
and s.definition is not null
and o.is_ms_shipped = 0
order by user_name(o.schema_id), o.name

How would I use the sp_msforeachdb to execute this. Also how will the resultes come out when you have several databases.

Your help is appriciated


What have you tried? Short of writing this for you there isn't much else to offer. There are plenty of examples online.

FWIW you should consider moving to the ANSI-92 style join instead of the older style join.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7824 Visits: 3696
jayoub1 (4/4/2013)
04/04/2013
I am a newbe database admin and I have been tasked to run queries against multiple databases. Each server has an instance and each instance has 10 or more databases. I need to know if there is an easy way to run a query on all the databases at once.

The way I do it now is by adding the database name each time. For example, the beginning of the query you have

USE [database name] ….

I type in the database name every time and execute and grab the results. This method was ok when the instance had only 1-2 production databases but now I have instances that have 10 or more databases plus the system databases. .

I tried using Registered Servers but it was not running against all the databases

Your help is appreciated.

Thanks,
Jeff


You can also try this http://www.sqlserverunlimited.com/?p=72
jayoub
jayoub
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1415 Visits: 587
Thanks for the help. I have not written anything yet, but will look at the examples provided.

Jeff
jayoub
jayoub
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1415 Visits: 587
I used the examples and figured it out. We start with

sp_MSForeachdb 'put select statement here' and it works

It gives a result for each database so since the instance has 26 database including system it will have that many resuts.

The other trick is that if the select statement you are using has quotation marks you replace them with brackes [...]

Example would be
Original Select query:
SELECT p.name 'User', r.name 'Role'
FROM sys.database_principals p, sys.database_principals r, sys.database_role_members m
WHERE p.principal_id = m.member_principal_id
AND r.principal_id = m.role_principal_id
AND m.role_principal_id = 16384
ORDER BY r.name, p.name

Modified Select Query with the SP:
sp_MSForeachdb 'SELECT p.name [User], r.name [Role]
FROM sys.database_principals p, sys.database_principals r, sys.database_role_members m
WHERE p.principal_id = m.member_principal_id
AND r.principal_id = m.role_principal_id
AND m.role_principal_id = 16384
ORDER BY r.name, p.name'

Thanks for the help.

Jeff
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search