Solved

For each syntax with Sharepoint databases

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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