Using sp_msforeachdb

Question

Using sp_msforeachdb

esso32

SSC Veteran

Points: 218
More actions
January 29, 2003 at 9:05 am

#159122

I'm new with SQL and i'm trying populate a table using sp_msforeachdb. Here is my code:
declare @DB_name varchar(25),
@Objectcount smallint
Exec sp_msforeachdb @command1= "use ?;select db_name() as
DB_name, count(*) as objectcount from sysobjects",
I'm trying to insert the data that was stored on DB_Name & objectcount to a table.
Any suggestions?

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

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

jpipes SSChampion Points: 12230 More actions · Answer 1

try this:



USE master



DECLARE @dbname CHAR(20), @name VARCHAR(12), @sql NVARCHAR(4000)



DECLARE dbs CURSOR FOR

SELECT name FROM sysdatabases



OPEN dbs

FETCH NEXT FROM dbs INTO @dbname

WHILE @@FETCH_STATUS = 0 BEGIN



SELECT @name = LTRIM(RTRIM(@dbname))



SELECT @sql = N'



SELECT 

  ''' + @name + ''' AS ''DB_NAME''

, COUNT(*) AS objectcount 

FROM ' + @name + '.dbo.sysobjects'



EXEC sp_executesql @sql



PRINT ''



FETCH NEXT FROM dbs INTO @dbname

END



CLOSE dbs

DEALLOCATE dbs



GO

esso32 SSC Veteran Points: 218 More actions · Answer 2

Thanks for your help. I was wondering what is the N for?

jpipes SSChampion Points: 12230 More actions · Answer 3

quote:
Thanks for your help. I was wondering what is the N for?

The "N" indicates that the string might contain unicode characters (in db names for instance), and so the executesql should treat the string as type NVARCHAR. This is more precautionary than anything. It just prevents some nasty, sometimes hard-to-diagnose bugs during execution...

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Just to show the sp_msforeachdb way as you tried initially.

Exec sp_msforeachdb @command1= 'select db_name() as

DB_name, count(*) as objectcount from ?..sysobjects'

Note don't worry with use in statement as you just need the 3 part name. Use ' instead of " which is standards not anything more. And I use [] around ? just to be sure of old things like DBs with numbers for names or white space in name but forum code gives issue here to see.

Also to do your insert then you just do.

Exec sp_msforeachdb @command1= 'INSERT dbname.objectower.tablename (col1, col2)select db_name() as

DB_name, count(*) as objectcount from ?..sysobjects'

Edited by - antares686 on 01/29/2003 2:27:01 PM