i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
ok, define the TOP 10 (since you can't do an intelligent TOP without ORDER BY)
since you were using sp_spaceUsed, i guess the top ten biggest tables?
what you'll need to do is create a temp table, and insert the results from each database into the temp table, and finally query the temp table results
i happen to have something saved in my snippets that gets space used for all tables in all databases, you can probably filter it from there with ROW_NUMBER() partition by database name.
edit: tested my code, i saved, and it's crappy;
building something else and will follow up with a post again
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!