none
How do you use sp_MSforeachdb against sys.objects?? ** NEED SOME HELP **

    Question

  • Does anyone know how I can include the database name in the @command1 variable?

    When I ran these statements, they worked fine.

    declare @RETURN_VALUE int
    DECLARE @command1 NVARCHAR(max)

    SET @command1 =
    '
    SELECT
    ''#''
    FROM sys.objects
    '
    exec @RETURN_VALUE = sp_MSforeachdb @command1 = @command1, @replacechar='#'


    It's going through every user database I have.  However, it's always returning the same
    number of sys.objects for the current database that I was on when I ran it in
    SSMS 2008.  The SELECT ''#'' produces the right database name except for the
    actual listing of the sys.objects entries.  If I was in "MyTestDB" when I ran it, I would
    get the sys.objects for "MyTestDB".

    When I tried to modify the above to look like:

     

    declare @RETURN_VALUE int
    DECLARE @command1 NVARCHAR(max)

    SET @command1 =
    '
    SELECT
    ''#''
    FROM ''#''.sys.objects
    '
    exec @RETURN_VALUE = sp_MSforeachdb @command1 = @command1, @replacechar='#'


    it produced syntax error.


    How can I get right listing for sys.objects for all DB's using sp_MSforeachdb


    Thank you.

    Monday, March 1, 2010 8:22 PM

Answers

  • The problem is that you are using the # to get the database name, but not applying it to the query successfully.

    First example, you are not actually changing to the next database, just using the database name.  You could code it by adding a USE command.

    SET @command1 =
    ' USE #

    SELECT
    ''#''
    FROM sys.objects
    '

    Second example, you kept the quote marks around the # in the name for dbname.sys.objects.  Removing those quote marks will make it back into a name that resolves to the database name.

    SET @command1 =
    '
    SELECT
    ''#''
    FROM #.sys.objects
    '

    It is always a little tricky working with dynamic-SQL and that is what you are creating here by using sp_MSforeachdb.

    Of course, neither of these is what you probably want to end up with, so maybe:

    SET @command1 =
    '
    SELECT
    ''#'' as dbname, object_name
    FROM #.sys.objects
    '
    This returns results like this:

    dbname name
    master sysrowsetcolumns
    master sysrowsets
    master sysallocunits
    master sysfiles1


    All the best,
    RLF
    Monday, March 1, 2010 9:07 PM

All replies

  • Remember, this is an undocumented sp.

    Try:


    EXEC sp_msforeachdb N'select ''[?]'' as [db_name], * from [?].sys.objects;';
    GO


    AMB
    Monday, March 1, 2010 8:33 PM
    Moderator
  • The problem is that you are using the # to get the database name, but not applying it to the query successfully.

    First example, you are not actually changing to the next database, just using the database name.  You could code it by adding a USE command.

    SET @command1 =
    ' USE #

    SELECT
    ''#''
    FROM sys.objects
    '

    Second example, you kept the quote marks around the # in the name for dbname.sys.objects.  Removing those quote marks will make it back into a name that resolves to the database name.

    SET @command1 =
    '
    SELECT
    ''#''
    FROM #.sys.objects
    '

    It is always a little tricky working with dynamic-SQL and that is what you are creating here by using sp_MSforeachdb.

    Of course, neither of these is what you probably want to end up with, so maybe:

    SET @command1 =
    '
    SELECT
    ''#'' as dbname, object_name
    FROM #.sys.objects
    '
    This returns results like this:

    dbname name
    master sysrowsetcolumns
    master sysrowsets
    master sysallocunits
    master sysfiles1


    All the best,
    RLF
    Monday, March 1, 2010 9:07 PM