Solved

For each syntax with Sharepoint databases

Posted on 2014-01-19
3
423 Views
Last Modified: 2014-01-20
There's incorrect syntax in the query below:

sp_MSforeachdb 'USE ?
SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
AND ps.database_id NOT IN (71,11,25,51,76)
ORDER BY ps.avg_fragmentation_in_percent DESC, page_count DESC'



There errors:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '('.
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'SharePoint_AdminContent_a96f6dbc'. No entry found with that name. Make sure that the name is entered correctly.

Note that:
'SharePoint_AdminContent_a96f6dbc is db_id = 11
0
Comment
Question by:barnesco
3 Comments
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 100 total points
ID: 39793350
This rings a bell...

I think I had a similar issue with those big long sharpoint DB names.
To solve it I think I put some "[" and "]" around the questionmark.

So.....

sp_MSforeachdb 'USE [?]
0
 
LVL 35

Accepted Solution

by:
David Todd earned 400 total points
ID: 39793393
Hi,

Try this:
execute master.dbo.sp_MSforeachdb '
USE [?]; 
SELECT 
	ps.database_id
	, ps.OBJECT_ID
	, ps.index_id
	, b.name
	, ps.avg_fragmentation_in_percent
	, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b 
	ON ps.OBJECT_ID = b.OBJECT_ID
	AND ps.index_id = b.index_id
WHERE 
	1 = 1
	--and ps.database_id = DB_ID()
	AND ps.database_id NOT IN (71,11,25,51,76)
ORDER BY 
	ps.avg_fragmentation_in_percent DESC
	, page_count DESC
'

Open in new window


What I did:
Put square brackets around the ? in the use,
Added a semicolon after the use statement
Added a dummy clause to the where
Commented out the ps.database_id clause in the where.
Formatted the code so it is easily read.

In my book there isn't any reason for dynamic code to be unreadable, just because its dynamic code.

Or in other words, because dynamic code has at least one extra layer of complexity, more effort should be put into its readability.

HTH
  David

PS Thanks to the guys above for the hint of the brackets, and that code did run on my SQL 2012 instance.
0
 

Author Closing Comment

by:barnesco
ID: 39794645
Thanks, I appreciate the help.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now