March 20, 2012 at 5:41 am
Hi,
How can I fetch table name, no of Rows and occupied size in MB from TSQL ?
March 20, 2012 at 5:47 am
Hi
Here is a piece of code that I use to accomplish this:
SET NOCOUNT ON
--Get a summary of the entire database.
EXEC sp_spaceused
--Declare needed variables.
DECLARE
@max-2 INT,
@min-2 INT,
@owner NVARCHAR(256),
@table_name NVARCHAR(256),
@sql NVARCHAR(4000)
DECLARE @table TABLE(
ident INT IDENTITY(1,1) PRIMARY KEY,
owner_name NVARCHAR(256),
table_name NVARCHAR(256))
IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL
BEGIN
DROP TABLE #results
END
CREATE TABLE #results(
ident INT IDENTITY(1,1) PRIMARY KEY, --Will be used to update the owner.
table_name NVARCHAR(256),
owner_name NVARCHAR(256),
table_rows INT,
reserved_space NVARCHAR(55),
data_space NVARCHAR(55),
index_space NVARCHAR(55),
unused_space NVARCHAR(55))
--Loop through statistics for each table.
INSERT @table(owner_name, table_name)
SELECT
su.name,
so.name
FROM
sysobjects so
INNER JOIN sysusers su ON so.uid = su.uid
WHERE
so.xtype = 'U'
SELECT
@min-2 = 1,
@max-2 = (SELECT MAX(ident) FROM @table)
BEGIN
SELECT
@owner = owner_name,
@table_name = table_name
FROM
@table
WHERE
ident = @min-2
SELECT @sql = 'EXEC sp_spaceused ''[' + @owner + '].[' + @table_name + ']'''
INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)
EXEC (@sql)
UPDATE #results
SET owner_name = @owner
WHERE ident = (SELECT MAX(ident) FROM #results)
END
SELECT * FROM #results
DROP TABLE #results
Hope it helps
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
March 20, 2012 at 5:50 am
Nice, thanks Andy.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 20, 2012 at 5:59 am
Andy thank you. this is really great 🙂
March 20, 2012 at 6:01 am
Your welcome 🙂
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
March 20, 2012 at 6:44 am
Thought I'd throw my own version of sp_SpaceUsed out there. Undoubtably, this will need to be updated some time in the near future but it works just fine in 2005 and doesn't require the use of any form of RBAR.
--_______________________________________________________________________________________________________________________
/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.
Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.
Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc are up to snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF. This command takes a while to execute if
-- you've never run it before but gets much shorter with repeated use.
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS
--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
--SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1, --Table with clustered index
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
--Jeff Moden
March 21, 2012 at 3:09 am
Just like to say thanks for posting these codes. 😀
March 22, 2012 at 2:46 am
Just tested your code out, Jeff--why does it seem to return a maximum 2048 for the max row size, even in cases where I know the maximum row size of the table is greater than that?
March 22, 2012 at 6:02 am
paul.knibbs (3/22/2012)
Just tested your code out, Jeff--why does it seem to return a maximum 2048 for the max row size, even in cases where I know the maximum row size of the table is greater than that?
Gosh. I've never noticed that before (I've not used the column myself for anything I've done) but you're right. :blush: It uses the XMaxLen column from the legacy dbo.sysindexes column. I'll have to look into it a bit more. I'm a bit shocked because the description for the column from BOL is "Maximum size of a row" but, considering it's from sysindexes documentation, I'm now thinking it's only for index rows (duh! Must have written it without the aid of proper amounts of coffee). That surprises me as well because the use of INCLUDE can make them a lot wider.
Thanks for pointing this out. I'll remove the columns for future postings.
{EDIT} I've also removed the columns from the code on this thread so that future readers don't run into a problem. My apologies on this. It's pretty embarassing.
--Jeff Moden
March 22, 2012 at 6:18 am
Here's a script I've saved and used from Tibor Karaszi's website. I adapted it slightly to filter table names based on a string at the start of the table name:
--List all tables in DB, adapted from Tibor Karaszi's SP at: http://www.karaszi.com/SQLServer/util_sp_tableinfo.asp
--See also sp_spaceused and zzQuery (superseded) for alternative approaches
--Written by Tibor Karaszi 2010-09-30
--Modified 2010-10-10, fixed rowcount multiplied by number of indexes.
--Modified 2010-10-11, fixed rowcount incorrect with BLOB and row overflow data.
DECLARE @tbl NVARCHAR(128);--sys.tables.name is a SYSNAME data type
SET @tbl = N'tmpRpt';
WITH t AS
(
SELECT
SCHEMA_NAME(t.schema_id) AS schema_name
,t.name AS table_name
,SUM(CASE WHEN p.index_id IN(0,1) AND a.type_desc = 'IN_ROW_DATA' THEN p.rows ELSE 0 END) AS rows
,SUM(CAST((a.total_pages * 8.00) / 1024 AS DECIMAL(9,2))) AS MB
,SUM(a.total_pages) AS pages
,ds.name AS location
FROM
sys.tables AS t
INNER JOIN sys.partitions AS p ON t.OBJECT_ID = p.OBJECT_ID
INNER JOIN sys.allocation_units AS a ON p.hobt_id = a.container_id
INNER JOIN sys.data_spaces AS ds ON a.data_space_id = ds.data_space_id
WHERE (@tbl IS NULL OR t.name LIKE @tbl + '%')
GROUP BY SCHEMA_NAME(t.schema_id), t.name, ds.name
)
SELECT schema_name, table_name, rows, MB, pages, location
FROM t
ORDER BY MB DESC, table_name
Rich
March 22, 2012 at 7:54 am
Jeff Moden (3/22/2012)
{EDIT} I've also removed the columns from the code on this thread so that future readers don't run into a problem. My apologies on this. It's pretty embarassing.
I intended no embarrassment, sorry--was just curious what might cause the effect! Will be interesting to see what the eventual answer to that is.
March 22, 2012 at 10:15 am
paul.knibbs (3/22/2012)
Jeff Moden (3/22/2012)
{EDIT} I've also removed the columns from the code on this thread so that future readers don't run into a problem. My apologies on this. It's pretty embarassing.
I intended no embarrassment, sorry--was just curious what might cause the effect! Will be interesting to see what the eventual answer to that is.
Oh no.... no problem. I'd have been pretty embarrassed even if I'd found it on my own and would have posted pretty much the same way. I really thank you for pointing out the flaw!
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply