Solved

For each syntax with Sharepoint databases

Posted on 2014-01-19
3
426 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

21 Experts available now in Live!

Get 1:1 Help Now