Stack Overflow
Stack Overflow

Questions tagged [sp-msforeachdb]

31 questions
5
votes
2 answers

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: DECLARE @cmd VARCHAR(8000); SET @cmd = 'USE ?; EXEC…
Michael
  • 477
  • 1
  • 5
  • 12
3
votes
1 answer

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 would truncate the logs of all databases on a…
Jaxidian
  • 13,081
  • 8
  • 83
  • 125
3
votes
2 answers

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 is working if I make it smaller and I noticed…
Stavros Koureas
  • 1,126
  • 12
  • 34
2
votes
3 answers

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 sp_msforeachdb 'Use ?; SELECT DB_NAME() AS DB, * FROM…
Vincent Kelleher
  • 492
  • 2
  • 6
  • 12
2
votes
1 answer

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 otherwise. This is my script EXEC…
Katie
  • 609
  • 1
  • 6
  • 17
1
vote
1 answer

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, and the view/tvf should contain the equivalent of…
Chronicide
  • 1,112
  • 1
  • 9
  • 32
1
vote
2 answers

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 manually verified that all 8 tables are…
Jeremiah Nelson
  • 93
  • 1
  • 6
1
vote
4 answers

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 are a few other databases in the server instance…
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
1
vote
1 answer

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 the code: sp_msforeachdb 'IF ''?'' like ''z%''…
Jeff
  • 852
  • 7
  • 17
  • 28
1
vote
1 answer

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
surenv2003
  • 119
  • 2
  • 4
  • 13
1
vote
1 answer

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 query that presents a list of how many contacts…
KJQ
  • 59
  • 1
  • 8
1
vote
4 answers

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 not, it should finish that iteration and move to…
Jonathon Anderson
  • 1,162
  • 1
  • 8
  • 24
1
vote
2 answers

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 table of database in list box! the code in C#: …
Arash
  • 3,013
  • 10
  • 52
  • 74
0
votes
2 answers

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 sp_MSforeachdb sp_MSforeachdb @command1 =…
Yogurt The Wise
  • 4,379
  • 4
  • 34
  • 42
0
votes
0 answers

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 is where I execute from. When I change it to…
Wynand Cilliers
  • 1
  • 1
1
2 3