December 29, 2015 at 2:23 am
I've below scripts which will give me table sizes for particular databases. My question is how to rewrite the code so it can return all tables sizes info from sql server regardless of what databases, meaning to say I don't need to change use_db.
[use_db]
SELECT t.NAME AS TableName,p.rows AS RowCounts,
SUM(a.total_pages) * 8 ASTotalSpaceKB,
SUM(a.used_pages) * 8 ASUsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 ASUnusedSpaceKB
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
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID>255
GROUP BY
t.Name, p.Rows
ORDER BY
t.Name
December 29, 2015 at 4:17 am
Quick solution
😎
USE master;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb.dbo.temp_result') IS NOT NULL DROP TABLE tempdb.dbo.temp_result;
CREATE TABLE tempdb.[dbo].[temp_result]
(
[DATABASE_NAME] [nvarchar](128) NOT NULL
,[TableName] [sysname] NOT NULL
,[RowCounts] [bigint] NOT NULL
,[TotalSpaceKB] [bigint] NOT NULL
,[UsedSpaceKB] [bigint] NOT NULL
,[UnusedSpaceKB] [bigint] NOT NULL
);
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
DECLARE @SIZE_QUERY NVARCHAR(MAX) = N'
USE {{@DB_NAME}}
INSERT INTO tempdb.[dbo].[temp_result]
(
[DATABASE_NAME]
,[TableName]
,[RowCounts]
,[TotalSpaceKB]
,[UsedSpaceKB]
,[UnusedSpaceKB]
)
SELECT
DB_NAME() AS [DATABASE_NAME]
,t.NAME AS TableName
,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
WHERE
t.NAME NOT LIKE ''dt%''
AND t.is_ms_shipped = 0
AND i.OBJECT_ID>255
GROUP BY
t.Name, p.Rows
ORDER BY
t.Name
';
SELECT @SQL_STR =
(
SELECT
REPLACE(@SIZE_QUERY,N'{{@DB_NAME}}',QUOTENAME( SDB.name ))
FROM sys.databases SDB
WHERE SDB.state_desc = N'ONLINE'
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)');
SELECT @SQL_STR;
-- uncomment after verifying the dynamic sql code!!
--EXEC ( @SQL_STR )
SELECT
[DATABASE_NAME]
,[TableName]
,[RowCounts]
,[TotalSpaceKB]
,[UsedSpaceKB]
,[UnusedSpaceKB]
FROM tempdb.[dbo].[temp_result];
December 29, 2015 at 12:07 pm
Borrowing Eirikur's query, you can always use sp_MsForEachDB
USE master;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb.dbo.temp_result') IS NOT NULL DROP TABLE tempdb.dbo.temp_result;
CREATE TABLE tempdb.[dbo].[temp_result]
(
[DATABASE_NAME] [nvarchar](128) NOT NULL
,[TableName] [sysname] NOT NULL
,[RowCounts] [bigint] NOT NULL
,[TotalSpaceKB] [bigint] NOT NULL
,[UsedSpaceKB] [bigint] NOT NULL
,[UnusedSpaceKB] [bigint] NOT NULL
);
DECLARE @SIZE_QUERY NVARCHAR(MAX) = N'
USE ?
INSERT INTO tempdb.[dbo].[temp_result]
(
[DATABASE_NAME]
,[TableName]
,[RowCounts]
,[TotalSpaceKB]
,[UsedSpaceKB]
,[UnusedSpaceKB]
)
SELECT
DB_NAME() AS [DATABASE_NAME]
,t.NAME AS TableName
,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
WHERE
t.NAME NOT LIKE ''dt%''
AND t.is_ms_shipped = 0
AND i.OBJECT_ID>255
GROUP BY
t.Name, p.Rows
ORDER BY
t.Name';
exec sp_MSforeachdb @Command1 = @SIZE_QUERY
SELECT
[DATABASE_NAME]
,[TableName]
,[RowCounts]
,[TotalSpaceKB]
,[UsedSpaceKB]
,[UnusedSpaceKB]
FROM tempdb.[dbo].[temp_result];
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply