February 22, 2008 at 4:16 pm
Okay, I must be losing my mind because i can't get this to work.
I have:
declare @dbname varchar(50)
set @dbname = 'db1'
SELECT A.object_id
, A.index_id
, [name]
, Avg_Fragmentation_In_Percent
FROM sys.dm_db_index_physical_stats (db_id(@dbname),NULL,NULL, NULL, NULL) AS A
JOIN sys.indexes B WITH(NOLOCK) ON A.Object_id = B.Object_id
AND A.Index_id = B.Index_id
WHERE Avg_Fragmentation_In_Percent >= 20
AND B.[name] IS NOT NULL
This piece of code gets me 0 result!
But if I change it to the db1 database and use just :
SELECT A.object_id
, A.index_id
, [name]
, Avg_Fragmentation_In_Percent
FROM sys.dm_db_index_physical_stats (db_id(), NULL,NULL, NULL, NULL) AS A
JOIN sys.indexes B WITH(NOLOCK) ON A.Object_id = B.Object_id
AND A.Index_id = B.Index_id
WHERE Avg_Fragmentation_In_Percent >= 20
AND B.[name] IS NOT NULL
I get information back. This 2nd piece of code doesn't use the @dbname variable.
How the hell do I get this to work so that I can pass in the @dbname variable to the db_id() function. Because right now, I have to actually move into the database I'm wanting to work on.
Thanks.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
February 22, 2008 at 5:30 pm
Okay, after looking into this some more ... I found out that the problem is when joining on sys.indexes.
if I'm on the master database, sys.indexes will only return the indexes that's in the master database, which it will try to compare to the indexes from database 'db1'.
The problem is now trying to tell the code to not use the working db but use the database 'db1' sys.indexes table.
'select * from db1.sys.indexes' will work, but
'select * from @dbname.sys.indexes' won't.
Anyone got any thoughts?
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
February 22, 2008 at 6:19 pm
man, this forum used to give me my information faster than I can figure it out.
Declare @db varchar(50)
select @db = 'db1'
declare @Sel nvarchar(120)
set @Sel = 'Select * from ' + @db + '.sys.indexes'
Print @Sel
exec sp_executesql @Sel
what I'm going to have to do is dump this out into a temp table and than run the join.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
February 23, 2008 at 10:00 pm
Since you have to resort to dynamic SQL anyways, don't bother with two statements and a temp table. You can do something like:
Declare @sql varchar(max);
Set @sql = 'Use ' + @db1 + '; ;';
Execute sp_executesql @sql;
HTH,
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply