September 22, 2014 at 8:35 am
hello
I wonder if anyone has used these three procedures together?
sp_msforeachdb and sp_msforeachtable and sp_spaceused
EXEC sp_MSForEachDB
@command1 = 'EXEC sp_MSforeachtable [?];',
@command2 = 'EXEC sp_spaceused [?]'
Go
I have about three hundred database in some instances
Approximately fifteen teradata in information
tks
September 22, 2014 at 8:39 am
Take a look at this post: http://www.sqlservercentral.com/Forums/Topic476475-8-1.aspx
This would also could be done with PowerShell with having to use ForEachDB and ForEachTable 🙂
September 22, 2014 at 10:03 am
aires (9/22/2014)
helloI wonder if anyone has used these three procedures together?
sp_msforeachdb and sp_msforeachtable and sp_spaceused
EXEC sp_MSForEachDB
@command1 = 'EXEC sp_MSforeachtable [?];',
@command2 = 'EXEC sp_spaceused [?]'
Go
I have about three hundred database in some instances
Approximately fifteen teradata in information
tks
Seems to me you just want table object sizes, correct?
This can be done as follows
CREATE TABLE #alldbobjects(
DBNameSYSNAMENULL
, SchemaNameSYSNAMENULL
, TableNameSYSNAMENULL
, IndexNameSYSNAMENULL
, IndexIDINTNULL
, IndexTypeVARCHAR(64)NULL
, IsDisabledINTNULL
, FG_or_PartVARCHAR(64)NULL
, PartitionNoINTNULL
, RowCntBIGINTNULL
, CompressionTypeVARCHAR(64) NULL
, AllocTypeVARCHAR(32) NULL
, TotalMBsBIGINTNULL
, UsedMBsBIGINTNULL
, DataMBsBIGINTNULL
)
INSERT INTO #alldbobjects
EXEC sp_msforeachdb @command1 = 'USE [?];
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECTDB_NAME()AS DBName
, s.nameAS SchemaName
, OBJECT_NAME(o.OBJECT_ID)AS TableName
, ISNULL(i.name, ''HEAP'')AS IndexName
, i.index_idAS IndexID
, CASE i.[type]
WHEN 0 THEN ''HEAP''
WHEN 1 THEN ''Clustered''
WHEN 2 THEN ''NonClustered''
WHEN 3 THEN ''XML''
WHEN 4 THEN ''Spatial''
ENDAS IndexType
, i.is_disabledAS IsDisabled
, CASE
WHEN i.data_space_id > 65600 THEN ps.name
ELSE f.name
ENDAS FG_or_Part
, p.partition_numberAS PartitionNo
, p.[rows]AS [RowCnt]
, p.data_compression_descAS CompressionType
, au.type_descAS AllocType
, au.total_pages / 128AS TotalMBs
, au.used_pages/ 128AS UsedMBs
, au.data_pages/ 128AS DataMBs
FROM sys.indexes i
LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] IN (1,3) THEN p.hobt_id
WHEN au.[type] = 2 THEN p.partition_id
END = au.container_id
WHERE o.is_ms_shipped <> 1
ORDER BY TotalMBs DESC'
SELECT * FROM #alldbobjects
WHERE DB_ID(DBName) > 4
ORDER BY DBName
DROP TABLE #alldbobjects
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 22, 2014 at 10:22 am
IF OBJECT_ID('tempdb.dbo.#sp_spaceused') IS NOT NULL
DROP TABLE #sp_spaceused
CREATE TABLE #sp_spaceused (
db_name varchar(100) NOT NULL CONSTRAINT sp_spaceused__DF_db_name DEFAULT '',
name varchar(100) NULL,
rows bigint NULL,
reserved varchar(20) NULL,
data varchar(20) NULL,
index_size varchar(20) NULL,
unused varchar(20) NULL
)
EXEC sp_MSforeachdb '
-- dbs to be ignored / excluded from processing
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
RETURN --exit with no (further) processing
-- add current db_name to the output
ALTER TABLE #sp_spaceused DROP CONSTRAINT sp_spaceused__DF_db_name
ALTER TABLE #sp_spaceused ADD CONSTRAINT sp_spaceused__DF_db_name DEFAULT ''?'' FOR db_name
INSERT INTO #sp_spaceused ( name, rows, reserved, data, index_size, unused )
EXEC sp_MSforeachtable @replacechar = ''#'', @command1 = ''EXEC sp_spaceused ''''#''''''
'
SELECT *
FROM #sp_spaceused
ORDER BY db_name, name
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 22, 2014 at 10:32 am
It looks like some decent scripts have been shared. I would caution against msforeachdb or msforeachtable due to some quirks that can cause databases or tables to be skipped when they are used.
You can get quite a bit more table size information from this script http://bit.ly/tablespace. Only thing needed would be to add an appropriate cursor to cycle through each database and produce the results for all databases.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply