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 ...
0
votes
1
answer
50
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
635
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
0
votes
1
answer
533
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
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
0
votes
1
answer
796
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
1
vote
4
answers
384
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
-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
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
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
2
votes
1
answer
275
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
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
3
votes
1
answer
697
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
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
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
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 ...
- 418k