Post reply

harnessing sp_msforeachdb to return one result set

  • March 29, 2013 at 1:16 pm

    #270487

    I found a nifty but unsupported stored procedure that loops through all databases on your server and allows you do to things to each database. I would like it to just return a list of db_users for each database.

    I tried executing as follows but I get a seperate result set for each loop:

    execute sp_msforeachdb 'use[?]; SELECT name, type, type_desc

    FROM sys.database_principals '

    Can someone show how to get only one result set listing all databases with corresponding users, type, and type_desc in one result set?

  • March 29, 2013 at 1:25 pm

    #1602136

    create a temp table, and instead of SELECTING, do INSERT INTO...SELECT:

    CREATE TABLE [dbo].[#TMP] (

    [DBNAME] NVARCHAR(256) NULL,

    [NAME] SYSNAME NOT NULL,

    [TYPE] CHAR(1) NOT NULL,

    [TYPE_DESC] NVARCHAR(120) NULL);

    execute sp_msforeachdb 'INSERT INTO #tmp SELECT ''?'' As DbName,name, type, type_desc FROM [?].sys.database_principals ';

    SELECT * FROM #tmp;

    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!

  • March 29, 2013 at 1:35 pm

    #1602139

    Thank you Lowell. I still Still STILL must learn to utilize these temp tables and other tools! Thank you for showing me.

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

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