February 21, 2012 at 5:11 am
When i use the following
sp_msforeachdb @command1='USE [chapter];SELECT * FROM sys.Tables'
it repeats the output for 23 times . Can any one please tell me the reason Why it is repeating the Output for 23 times? "The Database which i used contains 23 tables. Is it because of that?" . Please suggest.
February 21, 2012 at 5:17 am
I take it you want all the tables from each database?
sp_MSForEachDB 'USE [?]; SELECT * FROM sys.tables'
February 21, 2012 at 5:20 am
yes, I know the other way, but I want it using "sp_msforeachdb" . Please suggest
February 21, 2012 at 5:22 am
The T-SQL in my first post used sp_msforeachdb and loops through to get all records from sys.tables in each DB
February 21, 2012 at 5:26 am
I have the database name stored in a variable. lets say my databse name is 'chapter' .I want to retrieve all the all the table names of the database "chapter" . Its listing all the tables of the database "chapter" . But it is repeating for 23 times. I am not sure why it is repeating the same results for 23 times.
February 21, 2012 at 5:33 am
if you do
select count(*) from sys.databases
does it return 23 rows?
what your seeing is that you have used sp_msforeachdb but telling it to go back to the chapter database so what it is going is this
use master
go
use chapter
go
select * from sys.tables
go
use msdb
go
use chapter
go
select * from sys.tables
go
use model
go
use chapter
go
select * from sys.tables
go
etc
etc
etc
etc
Can you detail exactly what it is you want? Is it just all of the tables from one database? Is it all of the tables from ALL databases? Or is it something completly different?
February 21, 2012 at 5:38 am
No Anthony. When I query "Select cont(*) from Sys.databases" it returned only 9..not 23..
the thing is same output is being returned for 23 times.
February 21, 2012 at 5:40 am
Anthony,
I need all the tables from one database not from all the databases. Please tell me whats wrong with the following query
sp_msforeachdb 'USE [Chapter]; SELECT * FROM sys.Tables'
I clearly mentioned the Databse name not sure why it is looping for so many times.
February 21, 2012 at 5:49 am
if you only need data from one database then you dont need to use sp_msforeachdb.
issue the following and post the results
sp_msforeachdb 'USE [?]; SELECT TOP 1 * FROM sysobjects'
February 21, 2012 at 5:49 am
Hi Anthony,
Is it like I should not use that query when I want to retrieve all the tables from a single database.right?
February 21, 2012 at 5:56 am
yes that is correct
sp_msforeachdb will execute the query depending on the number of databases you have, so if you have 5 databases it runs the query 5 times.
strange how you say you only have 9 but loops 23 times, hence why I wanted you to run that query I gave you before, but I now realised its not right
please run this and post the results so we can see why it is running 23 times
sp_msforeachdb 'USE [?]; SELECT db_name()'
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply