April 4, 2013 at 7:50 am
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
April 4, 2013 at 9:14 am
Google "sp_msforeachdb".
April 4, 2013 at 9:39 am
Will do
Thanks.
Jeff
April 4, 2013 at 9:56 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 6, 2013 at 5:10 am
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
April 8, 2013 at 7:59 am
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
April 9, 2013 at 7:50 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2013 at 7:39 am
jayoub1 (4/4/2013)
04/04/2013I 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
April 10, 2013 at 8:37 am
Thanks for the help. I have not written anything yet, but will look at the examples provided.
Jeff
April 12, 2013 at 10:11 am
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
April 12, 2013 at 12:17 pm
I am finding that you have to replace the single quotes with double quotes in some cases.
Jeff
April 12, 2013 at 12:19 pm
I am finding tha I have to replace single quotes with double qoutes for the queries to work.
Jeff
April 12, 2013 at 12:43 pm
It would be advisable to use the ANSI-92 style joins instead of the much older comma separated list. Your query would look like this.
SELECT p.name as [User], r.name as [Role]
FROM sys.database_principals p
join sys.database_role_members m on p.principal_id = m.member_principal_id
join sys.database_principals r on r.principal_id = m.role_principal_id
WHERE m.role_principal_id = 16384
ORDER BY r.name, p.name
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply