question

Kumar V avatar image
Kumar V asked

Problem with SP_MSFOREACHDB in OPENROWSET

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

t-sqlsql-server-2000stored-procedures
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

any reason why this is a community wiki question?
0 Likes 0 ·
no, and I can't seem to change it back???
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

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)

7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
my version has USE [?]; in it
0 Likes 0 ·
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.
0 Likes 0 ·
And I corrected and tried. but problem not solved. i changed the code like ?..SYSINDEXES (NOLOCK) but no use.
0 Likes 0 ·
@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 '?'.
0 Likes 0 ·
Show more comments
Fatherjack avatar image
Fatherjack answered

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
10 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

so execute it with "USE [?];"
1 Like 1 ·
yes.. works well
0 Likes 0 ·
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.
0 Likes 0 ·
Yeah, you have to have the USE statement to get it to switch database context.
0 Likes 0 ·
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.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.