query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query question

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-03 : 02:59:28

i have this query:

exec sp_msforeachdb'
use ?
INSERT INTO #minatest
SELECT
db_name(database_id),
phystat.page_count,
i.fill_factor,
OBJECT_NAME(i.object_id),
i.name,
phystat.avg_fragmentation_in_percent,
newfragmentvalue = 0
FROM sys.dm_db_index_physical_stats(4, NULL, NULL, NULL, ''DETAILED'') phystat
JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id
WHERE phystat.avg_fragmentation_in_percent > 10 AND phystat.page_count < 10000
'

i want to change the 4 to a ? (so it changes automatically)

db_id(?) doesnt work

mr_mist
Grunnio

1870 Posts

Posted - 2007-01-03 : 03:17:12

I think that you need to prefix the table names with the [ ? ], so it would be like


FROM [ ? ].sys.dm_db_index_physical_stats

(do not have the spaces between the [ ] and the ?, I just put them there because otherwise it turns into a big question mark.)

-------
Moo. :)

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-03 : 03:43:38

The use statement will set the database context.
The problem is that the command needs the database id.
Unfortunately a null returns data for all databases.

You say that db_id() doesn't work but when I try
select top 10 * from sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'DETAILED')

It is fine and gets data for the current database
also
exec ('use master
select top 10 * from sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, ''DETAILED'') ')

seems to work so I suggest you try it again.
Could be that the insert query is causing a problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-03 : 18:01:34

yep ur right it does work in that sense but when used within the exec sp_msforeachdb command it would fail, please give it a try

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-03 : 18:07:09

when i use this its good and all but the parramater passed to sys.dm_db_index_physical_stats still will be null causing it to search threw all dbs

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-04 : 06:26:51

When I try it it doesn't - only returns data for the correct database.
Try
exec sp_MSforeachdb'
use ?
SELECT
db_name(database_id),
phystat.page_count,
i.fill_factor,
OBJECT_NAME(i.object_id),
i.name,
phystat.avg_fragmentation_in_percent,
phystat.database_id,
newfragmentvalue = 0
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, ''DETAILED'') phystat
JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id
WHERE phystat.avg_fragmentation_in_percent > 10 AND phystat.page_count < 10000
'

and see if you get multiple phystat.database_id's in a single resultset

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-04 : 17:54:42

thats because the sys.indexes section is limiting it to be the current database but this line here

FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, ''DETAILED'') phystat

is a waste of processing as it searches through all databases...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources