x

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

more ▼

asked May 27, 2010 at 06:47 AM in Default

Kumar V gravatar image

Kumar V
21 2 2 2

any reason why this is a community wiki question?
May 27, 2010 at 08:58 AM Fatherjack ♦♦
no, and I can't seem to change it back???
May 27, 2010 at 09:47 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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 
more ▼

answered May 27, 2010 at 08:17 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

yes.. works well
May 27, 2010 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, 2010 at 08:57 AM Kumar V
so execute it with "USE [?];"
May 27, 2010 at 09:24 AM Fatherjack ♦♦
Yeah, you have to have the USE statement to get it to switch database context.
May 27, 2010 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, 2010 at 10:15 AM Kumar V
(comments are locked)
10|1200 characters needed characters left

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)

more ▼

answered May 27, 2010 at 07:11 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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, 2010 at 07:31 AM Kumar V
my version has USE [?]; in it
May 27, 2010 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, 2010 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, 2010 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, 2010 at 08:17 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x474
x413

asked: May 27, 2010 at 06:47 AM

Seen: 2390 times

Last Updated: May 27, 2010 at 08:54 AM