Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query Multiple Databases Expand / Collapse
Author
Message
Posted Thursday, April 04, 2013 7:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:33 AM
Points: 85, Visits: 230
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
Post #1438809
Posted Thursday, April 04, 2013 9:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
Google "sp_msforeachdb".
Post #1438858
Posted Thursday, April 04, 2013 9:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:33 AM
Points: 85, Visits: 230
Will do

Thanks.


Jeff
Post #1438867
Posted Thursday, April 04, 2013 9:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
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 Moden's 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)
Post #1438882
Posted Saturday, April 06, 2013 5:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:54 AM
Points: 168, Visits: 674
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
Post #1439568
Posted Monday, April 08, 2013 7:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:33 AM
Points: 85, Visits: 230
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
Post #1439845
Posted Tuesday, April 09, 2013 7:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
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 Moden's 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)
Post #1440332
Posted Wednesday, April 10, 2013 7:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 3:44 PM
Points: 1,266, Visits: 2,912
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
Post #1440810
Posted Wednesday, April 10, 2013 8:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:33 AM
Points: 85, Visits: 230
Thanks for the help. I have not written anything yet, but will look at the examples provided.



Jeff
Post #1440850
Posted Friday, April 12, 2013 10:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:33 AM
Points: 85, Visits: 230
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
Post #1441788
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse