November 18, 2011 at 4:33 am
Hi
Trying to get the below query to work and having a little trouble, Basically if you run it as is, it will return the results of the current DB that you are in,
I want to be able to change the DB_ID() to the DB ID of another DB and get those results
However if i change the DB_ID() to say "6" for example i get nothing back? or the result of theDB im still in i.e "master"
SELECT DISTINCT
'[' + sm.[Name] + '].[' + OBJECT_NAME(s.[object_id]) + ']'
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.objects AS o ON s.[object_id] = o.[object_id]
INNER JOIN sys.schemas sm ON sm.[schema_id] = o.[schema_id]
WHERE s.avg_fragmentation_in_percent > 10
ORDER BY '[' + sm.[Name] + '].[' + OBJECT_NAME(s.[object_id]) + ']'
Any help will be much appreciated
Thanks
November 18, 2011 at 4:48 am
UnicornsRreal (11/18/2011)
HiTrying to get the below query to work and having a little trouble, Basically if you run it as is, it will return the results of the current DB that you are in,
I want to be able to change the DB_ID() to the DB ID of another DB and get those results
However if i change the DB_ID() to say "6" for example i get nothing back? or the result of theDB im still in i.e "master"
SELECT DISTINCT'[' + sm.[Name] + '].[' + OBJECT_NAME(s.[object_id]) + ']'
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.objects AS o ON s.[object_id] = o.[object_id]
INNER JOIN sys.schemas sm ON sm.[schema_id] = o.[schema_id]
WHERE s.avg_fragmentation_in_percent > 10
ORDER BY '[' + sm.[Name] + '].[' + OBJECT_NAME(s.[object_id]) + ']'
Any help will be much appreciated
Thanks
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = 'USE ' + QUOTENAME(name) + '
SELECT DISTINCT QUOTENAME(sm.[Name]) + ''.'' + QUOTENAME(OBJECT_NAME(s.[object_id]))
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.objects AS o ON s.[object_id] = o.[object_id]
INNER JOIN sys.schemas sm ON sm.[schema_id] = o.[schema_id]
WHERE s.avg_fragmentation_in_percent > 10
ORDER BY QUOTENAME(sm.[Name]) + ''.'' + QUOTENAME(OBJECT_NAME(s.[object_id]))'
FROM sys.databases
WHERE name = 'master'
EXEC(@SQL)
November 18, 2011 at 4:54 am
Damn that was quick!!!!
thanks 🙂
November 18, 2011 at 7:22 am
EXEC ('USE REMOTE_TEST
SELECT DISTINCT QUOTENAME(sm.[Name]) + ''.'' + QUOTENAME(OBJECT_NAME(s.[object_id]))
AS [Table] FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.objects AS o ON s.[object_id] = o.[object_id]
INNER JOIN sys.schemas sm ON sm.[schema_id] = o.[schema_id]WHERE s.avg_fragmentation_in_percent > 10
ORDER BY QUOTENAME(sm.[Name]) + ''.'' + QUOTENAME(OBJECT_NAME(s.[object_id]))
FROM sys.databases
WHERE name = REMOTE_TEST''') AT [LINKED_SERVER]
IS there away to get this query working using a linked server?
I tried but keep getting
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'FROM'.
November 18, 2011 at 7:26 am
What's the version of the db on the remote server?
This is missing a double quote before the db name.
WHERE name = REMOTE_TEST'''
November 18, 2011 at 7:30 am
If you're explicitly setting the "USE" value, then you don't need to query sys.databases anyway.
Try: -
EXEC ('USE [REMOTE_TEST]
SELECT DISTINCT QUOTENAME(sm.[Name]) + ''.'' + QUOTENAME(OBJECT_NAME(s.[object_id]))
AS [Table] FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.objects AS o ON s.[object_id] = o.[object_id]
INNER JOIN sys.schemas sm ON sm.[schema_id] = o.[schema_id]WHERE s.avg_fragmentation_in_percent > 10
ORDER BY QUOTENAME(sm.[Name]) + ''.'' + QUOTENAME(OBJECT_NAME(s.[object_id]))') AT [LINKED_SERVER]
November 18, 2011 at 7:36 am
I keep missing the simple things... sorry and thanks for your help
November 18, 2011 at 7:38 am
UnicornsRreal (11/18/2011)
I keep missing the simple things... sorry and thanks for your help
So long as you show that you've tried, no-one minds helping out 😉
November 18, 2011 at 7:44 am
Haha thanks! 🙂 You are a legend have a good weekend!!!:-D
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply