31 questions
0
votes
0
answers
28
views
DB_NAME() when looping through multiple databases
I have the following piece of script that loops through multiple identical databases (55 of them).
I am trying to get the database name as a column, but it keeps giving me the master db_name as this ...
1
vote
2
answers
1k
views
Invalid column name using sp_MSForEachDB in SQL Server 2016
I am attempting to query multiple databases housed on the same SQL Server instance using sp_MSForEachDB.
There are 8 databases that have the table man_days with a column named servicetype. I have ...
0
votes
1
answer
57
views
Reference of table in all databases of the Server
I want to find the reference of particular table in all the databases of the particular server.
Example :
My query window is opened for DB1 database and executed the query as:
exec sp_depends 'dbo....
0
votes
1
answer
231
views
How to exclude tempdb from sp_MSforeachdb?
Below I have a code to get info about t-log backups for each database in the server. I want to exclude tempdb from this list.
exec sp_MSforeachdb 'SELECT server_name, sysdb.name AS DatabaseName, bkup....
- 113
0
votes
1
answer
636
views
How should I do to properly use the sp_msforeachDB to create and delete backups
I'm setting up a new way to create and delete backups in our internal environments. The space on the servers is a big issue so i really have to find a way to do a backup and delete the older one for ...
- 93
1
vote
2
answers
1k
views
An alternative to sp_msforeachdb
I inherited the script below and struggling to run this against all databases across different instances.
It works when I use sp_msforeachdb but it does not output the way I want it to and it also ...
- 23
0
votes
1
answer
534
views
Use Temp Table to merge query results from different DBs
I need to extract data from different DBs into a single table. These DBs are all in the same Server and Instance and they have the same structure. One of the columns will be DB Name, the others come ...
- 2,132
4
votes
2
answers
4k
views
SQL Server Sp_msforeachdb query character llimitation
Recently I wrote a bigger query than I usually write and I got a SQL query error using the Sp_msforeachdb. This query is running for every database in the instance of SQL Server. In addition the query ...
- 1,517
0
votes
2
answers
2k
views
sp_MSForeachdb inserting into declared virtual table issue
I am having issues getting information to insert into the @TBL2 Table.
what am i doing wrong?
DECLARE @command varchar(1000)
DECLARE @SQLStatment varchar(1000)
DECLARE @TBL2 table (
...
- 17
1
vote
1
answer
79
views
TSQL - Averaging a result from sp_MSforeachdb
I have hundreds of databases on the same SQL server (SQL2012), they all have the same basic structure and I'm trying to get the average number of 'Contacts' our databases.
I can run a sp_MSforeachdb ...
- 59
0
votes
1
answer
799
views
SQL Server 2012- Using sp_msforeachdb
I want to run a SELECT statement on only some specific databases. The list of databases is returned by this query:
DECLARE @OneWeekAgo DATETIME
SELECT @OneWeekAgo = DATEADD(week,-1,GETDATE())
select ...
- 175
5
votes
2
answers
6k
views
How to use both sp_msforeachtable and sp_msforeachdb in the same query?
Is there any way to reference the table inside a 'sp_MSforeachtable' loop running inside a 'sp_msforeachdb' loop?
For example, in the following query the '?' is always referencing the database:
...
- 477
1
vote
4
answers
386
views
SQL Iterate Over All Tables
I am running the following code to extract all relevant rows from all tables that have a particular column. The outer IF is supposed to check if the column exists on the table for that iteration. If ...
- 1,218
0
votes
1
answer
3k
views
Running a Stored Procedure on All Databases, and Saving All Results Into A Table
I'm trying to create a query in which I cycle through all the databases in a server, run a stored procedure on it, and save it to a results table.
This is what I have thus far:
CREATE table results (...
- 15
-1
votes
1
answer
1k
views
sp_helprotect on all databases using sp_MSforeachdb [closed]
Anyone able to convert the following SQL query using sp_MSforeachdb?
I am using SQL Server 2005 SP4. Thanks!
DECLARE @tmpResults TABLE
(Owner sysname, Object sysname, Grantee sysname, Grantor sysname,...
- 39
0
votes
3
answers
6k
views
How to specify TABLE_SCHEMA name in sp_MSforeachdb to take database offline
I need to search for certain databases with a particular prefix. Once these databases have been located, I need to check to see if a certain schema exists and if it does not exist - take the database ...
- 3
0
votes
3
answers
1k
views
sp_msforeachdb in dataset
Can someone tell me why the following code only returns one row from the DataSet (for the master db) instead of one for each database on the server?
$SQLConn = New-Object System.data.SqlClient....
- 79
2
votes
3
answers
2k
views
Execute "sp_msforeachdb" in a Java application
Hi StackOverflow community :)
I come to you to share one of my problems...
I have to extract a list of every table in each database of a SQL Server instance, I found this query :
EXEC ...
- 530
0
votes
1
answer
4k
views
How can I omit system databases and allow SQL Server 2008 agent job to move past ERROR_NUMBER 208?
I have created a SQL Server 2008 Agent job that runs against all databases on the server. I am using the undocumented MS procedure, sp_MSforEachDB. I am specifying only certain databases to be ...
- 3
0
votes
3
answers
5k
views
"Looping" through databases with sp_MSforeachdb and returning 1 data set
So, I've been wrestling with the code I found on my buddy's website:
8 Steps to Moving Database Logins
I want to generate the Database Level Security, Roles, and Explicit Permissions statements in ...
- 3,199
2
votes
1
answer
277
views
Run operations on all the tables in all the databases
I'm trying to create a SQL Server script that applies some operations to all the tables in all the databases. I need to rename some tables if some conditions are respected, truncate the tables ...
- 659
1
vote
2
answers
736
views
Sp_msforeachtable Returns Just First Table In C#
I Want To List Tables Of a database with sp_msforeachtable.i did it well in SSMS(i wrote it in sp_list2 stored procedure), but when i want to use this stored procedure in c#,it justs return first ...
- 3,081
0
votes
2
answers
4k
views
SQL get table row count for specific table in DB
How Can i iterate thru the all my DB's and get a row count for each employee table?
Each client is has there own DB, need to find the total employees in each DB.
Been trying to figure out how to use ...
- 4,499
1
vote
1
answer
651
views
How to create a table valued function or view that returns the results of a query ran against multiple databases
I need to create a view or a table valued function that returns one result set from a query ran against a dynamic list of databases (stored in a table). All of the databases have the same structure, ...
- 1,150
3
votes
1
answer
699
views
Unexpected sp_MSForEachDB behavior
I'm working to enhance my understanding of some system sprocs and I'm very confused by this script I was working on. To exercise my understanding of sp_MSForEachDB I decided to write a script that ...
- 13.6k
0
votes
1
answer
127
views
i want to store size of the log file in a temp table
what is the wrong with
insert into #tempA(MailSize)
EXEC sp_foreachdb_dev @command = N' USE ?
SELECT (size * 8)/1024.0 AS size_in_mb FROM
sys.database_files WHERE
data_space_id = 0',
@...
- 119
1
vote
1
answer
1k
views
Using a Hidden Stored Procedure
I am trying to use the following stored procedure to obtain databases from a server. However, I just want the databases created from a month ago. What do I have to do to make this work?
Here is ...
- 882
1
vote
1
answer
1k
views
I want to update a table in each db on server
I have a config table with same fields "SMTP_Server". I want to update the fields with new value in each database on the server.
sp_foreachdb updates last table (in last database). Why?
please help
- 119
0
votes
2
answers
7k
views
sp_MSforeachdb query help
I'm working with a lot of databases that are the same so I am using the sp_MSforeachdb procedure so that I can retrieve information from a table.
The problem I have encountered is that there are ...
- 1,613
0
votes
2
answers
2k
views
sp_MSforeachdb MS SQL error
This is for MS SQL 2005: Anyone know why the first succeeds, then creating two of the same statement in succession fails? All the statements are exactly the same. Changing the double quote to two ...
- 21.3k
1
vote
4
answers
7k
views
Help with sp_msforeachdb -like queries
Where I'm at we have a software package running on a mainframe system. The mainframe makes a nightly dump into sql server, such that each of our clients has it's own database in the server. There ...
- 419k