sp_msforeachdb not working as expected

Question

Post reply

sp_msforeachdb not working as expected

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
June 28, 2010 at 12:38 am

#236973

Hi All
I was trying to get the max length of table name in each database and used this query:
exec master..sp_msforeachdb 'select max(len(name)) from sys.tables'
but it returns max length from one databse only ,although records are repeated for each database.
Am I missing something here??
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

sp_msforeachdb uses the ? placeholder for the current database being processed.

This should do the trick.

exec master..sp_msforeachdb 'select max(len(name)) from [?].sys.tables'

-- Gianluca Sartori

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

Thanks for the reply..it makes me wonder why at times we dont have to supply a placeholder and it still works

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Lowell SSC Guru Points: 323518 More actions · Answer 3

suresh kumar Sulakh (6/28/2010)
Thanks for the reply..it makes me wonder why at times we dont have to supply a placeholder and it still works

that's because the command is repeating over and over in the same database you are currently connected in; the sp_msForEachDb is just the cursor that runs it for each database.

compare the results of these two:

exec master..sp_msforeachdb 'select ''?'' as dbName, db_name() as CurrDB,max(len(name)) as mxLen from ?.sys.tables'

exec master..sp_msforeachdb 'select ''?'' as dbName, db_name() as CurrDB,max(len(name)) as mxLen from sys.tables'

the second one, which is not using the ? placeholder, is just getting the same length over and over again, where the first one is getting it for each db because of the placeholder changing the db context.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!