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
Toggle Comment visibility. Current Visibility: Viewable by all users

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

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

2 Answers

·
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
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Kumar V avatar image Kumar V commented ·
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 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
my version has USE [?]; in it
0 Likes 0 ·
Kumar V avatar image Kumar V commented ·
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 ·
Kumar V avatar image Kumar V commented ·
And I corrected and tried. but problem not solved. i changed the code like ?..SYSINDEXES (NOLOCK) but no use.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
@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 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
@Grant I use this sort of thing in a few places at work and have to wrap in [ and ] as some 3rd party databases have spaces in the names!!! Go figure! Have re-written script to give what I think is required and posted as another answer
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
@FatherJack it's just one of those things that I've had to juggle with a few times to get work right. The brackets have occassionally given me troubles, but not all the time. I'm sure it's just a silly formatting error, but I'm frequently lazy & just take the path of least resistance.
0 Likes 0 ·
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
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Fatherjack avatar image Fatherjack ♦♦ commented ·
so execute it with "USE [?];"
1 Like 1 ·
Kumar V avatar image Kumar V commented ·
yes.. works well
0 Likes 0 ·
Kumar V avatar image Kumar V commented ·
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 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Yeah, you have to have the USE statement to get it to switch database context.
0 Likes 0 ·
Kumar V avatar image Kumar V commented ·
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.