Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

For each syntax with Sharepoint databases

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
barnesco
Asked:
barnesco
2 Solutions
 
Tony303Commented:
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
 
David ToddSenior DBACommented:
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
 
barnescoAuthor Commented:
Thanks, I appreciate the help.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now