November 21, 2013 at 2:33 pm
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.
Thanks
George
November 21, 2013 at 3:00 pm
gchappell (11/21/2013)
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.Thanks
George
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
Lowell
November 21, 2013 at 3:27 pm
this is much better:
IF EXISTS (SELECT OBJECT_ID('tempdb.dbo.#tmp'))
DROP TABLE #tmp
CREATE TABLE [dbo].[#tmp] (
[DBName] NVARCHAR(128) NULL,
[TableName] SYSNAME NOT NULL,
[SchemaName] SYSNAME NULL,
[RowCounts] BIGINT NOT NULL,
[TotalSpaceKB] BIGINT NULL,
[UsedSpaceKB] BIGINT NULL,
[UnusedSpaceKB] BIGINT NULL)
EXEC sp_MsForEachDB '
USE [?];
INSERT INTO #tmp
SELECT
db_name() AS DBName,
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ''dt%''
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name'
--top 10 each db
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY DBName ORDER BY TotalSpaceKB DESC) As RW,*
FROM #tmp ) MyAlias
WHERE RW <=10
Lowell
November 21, 2013 at 4:15 pm
Thank you Lowell this worked great. Just what i was trying to create myself with no success.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply