Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using Sys tables to get database and table size.

Author  Topic 

Bjcascone
Starting Member

37 Posts

Posted - 2010-08-17 : 16:42:16
Does anyone have a script that uses the sys tables to obtain all of the databases and tables contained within the DB Sizes? I am just looking for a start to write something that will suite my needs. I do not have that much experience with sys tables.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-17 : 16:48:24
Could you instead just use the built-in report in SSMS?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Bjcascone
Starting Member

37 Posts

Posted - 2010-08-17 : 16:58:47
The SSMS reports are useful on a one off basis but I am hoping to find a solution that can be used for all of the DB's and tables and put them into a view.

what i am trying to do is create a view that will show a list of all of the databases and the tables that are associated with them, and eventually the size of each table, size of any indexes on the tables, log files. (a few other thins as well but those are the main things i am looking to have in this view)

I know that i can access all of this data from the Sys tables i am just not sure where to locate it all.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-17 : 17:43:59
Check the Script Library forum here and search for the scripts that Michael Valentine Jones wrote. He's got several size scripts in there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-17 : 21:08:17
I wrote a quick and dirty script a while back. I use it to track table growth. Every week I add records to this table

USE [dba]
GO
CREATE TABLE [dbo].[AllTableHistory](
[db] [sysname] NOT NULL,
[schemaName] [sysname] NOT NULL,
[tbl] [sysname] NOT NULL,
[rows] [int] NOT NULL,
[dt] [smalldatetime] NOT NULL,
[reservedKB] [int] NULL,
[dataKB] [int] NULL,
[index_sizeKB] [int] NULL,
[unusedKB] [int] NULL,
CONSTRAINT [pk_allTblHist_100] PRIMARY KEY CLUSTERED
(
[db] ASC,
[schemaName] ASC,
[tbl] ASC,
[dt] ASC
)
)
GO


And here's the script to populate it

-- hold space used values
Create table #t (
tbl sysname,
rows int,
reserved varchar(32),
data varchar(32),
index_size varchar(32),
unused varchar(32)
)

declare @db sysname
declare @schema sysname
declare @tbl sysname

-- get all tables, schema, rowcounts
Declare c Cursor
Read_Only
FOR
select name from sys.databases where database_id > 4
open c
fetch next from c into @db
while @@FETCH_STATUS = 0
begin
EXEC('
INSERT INTO AllTableHistory (db, schemaname, tbl, rows, dt)
SELECT ''' + @db + ''', s.name, t.name, max(rows), DATEADD(day, 0, datediff(day, 0, getdate()-1))
FROM [' + @db + '].sys.[tables] t
JOIN [' + @db + '].sys.[partitions] p
on t.object_id = p.object_id
JOIN [' + @db + '].sys.[schemas] s
on s.schema_id = t.schema_id
group by
s.name, t.name
')
fetch next from c into @db
end
close c
deallocate c

-- get space used info
DECLARE c Cursor Read_Only
FOR
SELECT db, schemaname, tbl from AllTableHistory WHERE dt = DATEADD(day, 0, datediff(day, 0, getdate()-1)) ORDER BY db
Open c
Fetch Next From c INTO @db, @schema, @tbl
while @@FETCH_STATUS = 0
begin
truncate table #t
insert #t
exec (
'use [' + @db + '];
exec sp_spaceused ''' + @schema + '.' + @tbl + ''''
)

update AllTableHistory
set reservedKB = Convert(int, Replace(t.reserved, 'KB', '')),
dataKB = Convert(int, Replace(t.data, 'KB', '')),
index_sizeKB = Convert(int, Replace(t.index_size, 'KB', '')),
unusedKB = Convert(int, Replace(t.unused, 'KB', ''))
from #t t
join AllTableHistory h
on t.tbl = h.tbl
where h.db = @db
And h.schemaName = @schema
and h.dt = DATEADD(day, 0, datediff(day, 0, getdate()-1))
Fetch Next From c INTO @db, @schema, @tbl
end
close c
deallocate c

drop table #t


Now, none of this does any good if you aren't actively monitoring it.

Also, I restore a LOT of production databases to a server that is used for (among other things) (1) analyzing data patterns and (2) making sure our backups will restore I have a job that restores many of our most important databases every night.

Hope this helps. Here is a very simple ad-hoc query you can run (again using a cursor but it returns pretty quick

Create table #t (
tbl sysname,
rows int,
reserved varchar(32),
data varchar(32),
index_size varchar(32),
unused varchar(32)
)

Declare @tbl sysname
Declare c Cursor
read_only
for
select name from sys.tables
--select name from sysobjects where type = 'u'
open c
fetch next from c into @tbl
while @@fetch_status = 0
begin
insert #t
exec sp_spaceused @tbl
fetch next from c into @tbl
End
Close c
Deallocate c
select * from #t
drop table #t

Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-18 : 03:58:24
Russels script looks like it's covering your requirements quite well! Here is a much smaller script for a single database:
SET NOCOUNT ON

DECLARE @SpaceUsed table (
TableName SYSNAME,
TableRows INT,
TableSize VARCHAR(10),
DataSpaceUsed VARCHAR(10),
IndexSpaceUsed VARCHAR(10),
UnusedSpace VARCHAR(10)
)

DECLARE
@tablename NVARCHAR(200),
@tableschema NVARCHAR(200),
@cmd NVARCHAR(200)

DECLARE c1 CURSOR
FOR SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'base table'

OPEN c1
FETCH c1 INTO @tableschema, @tablename
WHILE @@fetch_status = 0
BEGIN
SET @cmd = 'exec sp_spaceused[' + @tableschema + '.' + @tablename + ']'
INSERT INTO @SpaceUsed
EXEC sp_executesql @cmd

FETCH NEXT FROM c1 INTO @tableschema, @tablename
END

CLOSE c1
DEALLOCATE c1

SELECT * FROM @SpaceUsed ORDER BY TableName


- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

Bjcascone
Starting Member

37 Posts

Posted - 2010-08-19 : 16:29:08
Russell and Lumbago, you guys rock! this is a great start for me. I appreciate the help.
Go to Top of Page
   

- Advertisement -