Problem with SP_MSFOREACHDB in OPENROWSET

Question

i able to execute

EXEC SP_MSFOREACHDB 'SELECT ''?'', object_name(id), name, indid, 
INDEXPROPERTY(id, name, ''IsPageLockDisallowed'') AS IsPageLockDisallowed, 
INDEXPROPERTY(id, name, ''IsRowLockDisallowed'') AS IsRowLockDisallowed
FROM sysindexes (nolock)
where INDEXPROPERTY(id, name, ''IsPageLockDisallowed'') = 1
OR INDEXPROPERTY(id, name, ''IsRowLockDisallowed'') = 1'

it executed for all databases but

SELECT a.* 
FROM 
OPENROWSET('SQLNCLI', 'DRIVER=SQL Server;Server=MyServer;trusted_connection=yes;',  
     'SET NOCOUNT ON;SET FMTONLY OFF;         
EXEC SP_MSFOREACHDB ''SELECT ''''?'''', object_name(id), name, indid, 
INDEXPROPERTY(id, name, ''''IsPageLockDisallowed'''') AS IsPageLockDisallowed, 
INDEXPROPERTY(id, name, ''''IsRowLockDisallowed'''') AS IsRowLockDisallowed
FROM sysindexes (nolock)
where INDEXPROPERTY(id, name, ''''IsPageLockDisallowed'''') = 1
OR INDEXPROPERTY(id, name, ''''IsRowLockDisallowed'''') = 1''') as a

it executes only for master database or if i specified database name in connection string it executes only for the specified database.

what could be the solution to execute the second query in all databases?

Thanks in advance.. Kumar

Answer 1

0

Try:

SELECT a.* FROM OPENROWSET('SQLNCLI', 'DRIVER=SQL Server;Server=MyServer;trusted_connection=yes;',  
     'SET NOCOUNT ON;SET FMTONLY OFF;         
EXEC SP_MSFOREACHDB 'USE [?];SELECT ''''?'''', object_name(id), name, indid, 
INDEXPROPERTY(id, name, ''''IsPageLockDisallowed'''') AS IsPageLockDisallowed, 
INDEXPROPERTY(id, name, ''''IsRowLockDisallowed'''') AS IsRowLockDisallowed
FROM sysindexes (nolock)
where INDEXPROPERTY(id, name, ''''IsPageLockDisallowed'''') = 1
OR INDEXPROPERTY(id, name, ''''IsRowLockDisallowed'''') = 1''') as a

Edit
noticed apostrophe in wrong place. please try now (12:03 GMT+1)

answered May 27 '10 at 07:11 AM

Fatherjack ♦♦
38.8k ● 56 ● 73 ● 104

I tried it but problem still exists. and i could not find any difference between yours and mine. if you mention the changes you done it will more useful to me to understand.

Thanks to Fatherjack for your quick reply. – Kumar V

May 27 '10 at 07:31 AM Kumar V

my version has USE [?]; in it

May 27 '10 at 08:03 AM Fatherjack ♦♦

Now i am getting the syntax error like

Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'USE'. Msg 911, Level 16, State 1, Line 4 Could not locate entry in sysdatabases for database '?'. No entry found with that name. Make sure that the name is entered correctly.

May 27 '10 at 08:10 AM Kumar V

And I corrected and tried. but problem not solved. i changed the code like ?..SYSINDEXES (NOLOCK) but no use.

May 27 '10 at 08:14 AM Kumar V

@FatherJack you're on the right track. You just have to get the USE command to work. It's the context that matters. Instead of [?] try wrapping it in quotes, doubling as necessary to get '?'.

May 27 '10 at 08:17 AM Grant Fritchey ♦♦

(comments are locked)

Answer 2

0

OK, does this give you the results you want?

CREATE TABLE #IndexData
    (
      DbName sysname,
      objName VARCHAR(100),
      otherName varchar(100),
      indid INT,
      p_lock INT,
      r_lock int
    )
INSERT  INTO #IndexData
        (
          DbName,
          objName,
          otherName,
          indid,
          p_lock,
          r_lock
        )
        EXEC SP_MSFOREACHDB 'USE [?];SELECT ''?'', object_name(id), name, indid, 
INDEXPROPERTY(id, name, ''IsPageLockDisallowed'') AS IsPageLockDisallowed, 
INDEXPROPERTY(id, name, ''IsRowLockDisallowed'') AS IsRowLockDisallowed
FROM sysindexes (nolock)
where INDEXPROPERTY(id, name, ''IsPageLockDisallowed'') = 1
OR INDEXPROPERTY(id, name, ''IsRowLockDisallowed'') = 1'

SELECT  *
FROM    #IndexData
go
DROP TABLE #IndexData

answered May 27 '10 at 08:17 AM

Fatherjack ♦♦
38.8k ● 56 ● 73 ● 104

yes.. works well

May 27 '10 at 08:47 AM Kumar V

if i do not use USE [?], i am getting results but the changes are only in dbnames but all other columns are from master database only. i.e. i am getting 6 rows for every database which is the result of master database.

May 27 '10 at 08:57 AM Kumar V

so execute it with "USE [?];"

May 27 '10 at 09:24 AM Fatherjack ♦♦

Yeah, you have to have the USE statement to get it to switch database context.

May 27 '10 at 09:30 AM Grant Fritchey ♦♦

Please forgive me to disturb you. i tried it immediately as you replied but no use. still it return only 6 rows which is the result of master database.

May 27 '10 at 10:15 AM Kumar V

(comments are locked)

Problem with SP_MSFOREACHDB in OPENROWSET

Follow this question

Related Questions