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

Find out the size of database tables, row count, data size, index growth Expand / Collapse
Author
Message
Posted Sunday, November 18, 2012 7:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 11:35 AM
Points: 7, Visits: 76
I want to create table and all columns. column names are database name, current date, row count, data size, index size.

in this table i want to insert about all the tables above information for one particular database.

I want to use DMV for this

let me know if you have any question.
Post #1386046
Posted Sunday, November 18, 2012 10:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
sys.databases
sys.tables
sys.indexes
sys.dm_db_partition_stats



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1386064
Posted Sunday, November 18, 2012 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 11:35 AM
Points: 7, Visits: 76
do you have a script for this or can you send me the script please?
Post #1386067
Posted Sunday, November 18, 2012 10:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
Not offhand, no. It's not hard though, most of the data you want is in sys.dm_db_partition_stats, you just need to join to the others to get the names.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1386069
Posted Sunday, November 18, 2012 11:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
This might be a good start.

use MyDB
go

DECLARE @names TABLE
(
id INT IDENTITY(1,1),
name NVARCHAR(100) NULL
)
DECLARE @ROWCOUNT INT
DECLARE @i INT = 1
DECLARE @str nvarchar(100)

INSERT INTO @names(name) SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT

DECLARE @space TABLE
(
name NVARCHAR(100) NULL,
rows CHAR(11),
reserved NVARCHAR (15),
data NVARCHAR (18),
indexes NVARCHAR (18),
unused NVARCHAR (18)
)
WHILE @i <= @ROWCOUNT
BEGIN
SELECT @str = name FROM @names WHERE id = @i
INSERT INTO @space
EXEC sp_spaceused @str
SET @i += 1
END
SELECT * FROM @space
ORDER BY CONVERT( BIGINT, rows ) DESC
;

Post #1386072
Posted Sunday, November 18, 2012 11:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 11:35 AM
Points: 7, Visits: 76
Thanks for the reply.
this is now working out.
can you help me with the script using dmv
It creates table and insert all the tables information about specified database
Post #1386079
Posted Sunday, November 18, 2012 12:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
tinakothari (11/18/2012)
Thanks for the reply.
this is now working out.
can you help me with the script using dmv
It creates table and insert all the tables information about specified database


All of the information you seek is in the system objects that Gail Shaw noted in her post above. If you need the information you state, then you should understand that information, as well. The only way you'll understand all of that information is to read about each of those objects in Books Online and figure this one out on your own. You won't be sorry that you did. Think of it as a "rite of passage" that every DBA should go through. If you have problems during your attempt, I'm sure that folks would be happy to help you with specific prroblems instead of writing the entire query for you.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386088
Posted Sunday, November 18, 2012 2:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 6,752, Visits: 14,397
tinakothari (11/18/2012)
I want to create table and all columns. column names are database name, current date, row count, data size, index size.

in this table i want to insert about all the tables above information for one particular database.

I want to use DMV for this

let me know if you have any question.


You could try this

SELECT			DB_NAME() AS DatabaseName
, object_name(i.object_id) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.index_id AS IndexID
, i.type_desc AS IndexType
, p.partition_number AS PartitionNo
, p.[rows] AS NumRows
, au.type_desc AS InType
, au.total_pages AS NumPages
, au.total_pages * 8 AS TotKBs
, au.used_pages * 8 AS UsedKBs
, au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_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
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.is_ms_shipped <> 1
ORDER BY TableName, i.index_id



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1386101
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse