Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query to return top 10 tables on each db on server Expand / Collapse
Author
Message
Posted Thursday, November 21, 2013 2:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:47 AM
Points: 25, Visits: 108
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
Post #1516588
Posted Thursday, November 21, 2013 3:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 12,905, Visits: 32,182
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1516596
Posted Thursday, November 21, 2013 3:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 12,905, Visits: 32,182
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1516604
Posted Thursday, November 21, 2013 4:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:47 AM
Points: 25, Visits: 108
Thank you Lowell this worked great. Just what i was trying to create myself with no success.
Post #1516608
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse