0

Trying to run a more complex SQL query on an MS SQL server, but I get no data output. Logically should be fine, but I cant see why it doesnt work:

SELECT D.NAME DATABASE_NAME,T.NAME TABLE_NAME,I.NAME INDEX_NAME,C.NAME COLUMN_NAME FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, 'LIMITED') XXX INNER JOIN SYS.DATABASES D ON D.DATABASE_ID = XXX.DATABASE_ID INNER JOIN SYS.TABLES T ON T.OBJECT_ID = XXX.OBJECT_ID INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = T.OBJECT_ID INNER JOIN SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = I.OBJECT_ID INNER JOIN SYS.COLUMNS C ON C.OBJECT_ID = IC.OBJECT_ID ORDER BY D.NAME, T.NAME

If I run SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, 'LIMITED'), I get proper data, so the other query should work, but still doesn't.

Any ideas?

0

I would imagine that you are running this in master.. the sys.dm_db_index_physical_stats reports at global level, the other tables are at a database level.

you can confirm this by USE and executing you should see data returned. You may also want to use sp_msforeachDB ( http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx) to execute the code in each database.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.