Script to gain capacity information
Recently at my site we had a need to gather current capcatiy of all databases as well as table obejcts on our servers. although there are some out of the box stored proceudres to assist in this effort as well as some schema objects, they mostly would return information on one database at a time. Not to mention we wanted to foramt this information into a report that could be distrubuted to server administrators and management. the follwoing two scripts will return inforamtion for all databases on server as well as individual table inforamtion such as primary key size, index size, total records etc.you can then take these results and paste into excel, i have a dts as well that will paste for me but i belive that is another topic. please note these procs will call system tables and objects so sa privileges will be required.PLEASE NOTE THESE PROCS SHOULD BE PALCED INTO MASTER DB AND IN QUERY ANALYZER SET TABS AT 4. enjoy
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/******************************************************************************
* File Name: dbo.usp_capacity_db
* File Desc: Will get total capactity of all databases on the server as
* well as individual file sizes for each database.
* Database: MASTER
* Language: MS Transact-SQL
* Version: MS SQL 2000
*
* Tables:
* SYSDATABASE,LOCAL SYSFILES
*
*
* Procedures: N/A
*
* Date: 9/09/2002
* Author: KILEY MILAKOVIC(MCDBA)
* Architect:
* Project Mgr: *******************************
* Project ID: N/A
*
* Notes:
* 1.******************************
*
* Special Comments/Warnings
* None
*
*******************************************************************************
* Procedure Name: usp_capacity_db
*
* Procedure Desc: Will get total capactity of all databases on the server as
* well as individual file sizes for each database.
*
* Parameters:
* @projected_size = DEFAULT IS NULL, IF YOU WISH TO FUTURE PROJECT
* GIVE INT WILL TAKE CURRENT SIZE AND MULTIPLY BY
* GIVEN AMOUNT OF YARS.
*
* RETURNs: N/A
*
* Notes:
* None
*
*******************************************************************************
* Version: xxxx Author: xxxxxxxxxxxxxxxxxxx
* Date: xx/xx/xxxx
*******************************************************************************
* Description of Requests:
* 1.
*
* Description of Modifications:
* 1.
*
* Special Comments:
* None
*
* Other modules changed with this request:
* None
*******************************************************************************/
CREATE PROCEDURE usp_capacity_db @projected_size nvarchar(2)
AS
declare @db_count int,
@loop_count bigint,
@sqlstat varchar(7000),
@sqlstat2 varchar(7000),
@sqlstat3 varchar(7000),
@sqlstat4 varchar(7000),
@db_name varchar(75)
CREATE TABLE #capacity
(
tb_id int identity,
database_name varchar(75),
database_id int,
total_database_size nvarchar(30) null
)
CREATE TABLE #capacity2
(
database_name varchar(75),
database_file_name varchar(100),
database_file_id int,
database_file_size nvarchar(15)
)
/*******************************************************************************
**GET LIST OF DATABASES
*******************************************************************************/
SET @loop_count = 1
IF @projected_size is null or @projected_size = '0'
SET @projected_size = '1'
INSERT INTO #capacity(database_name,database_id)
SELECT UPPER(name),dbid
FROM sysdatabases
WHERE version is not null
SELECT @db_count = count(*)
FROM #capacity
/*******************************************************************************
**GET LIST OF FILES AND SIZES FOR EACH DATABASE
*******************************************************************************/
WHILE @loop_count < @db_count
BEGIN
SELECT @db_name = database_name
FROM #capacity
WHERE tb_id = @loop_count
SET @sqlstat = 'use ' + @db_name
SET @sqlstat2 = ' insert into #capacity2(database_name,database_file_name,database_file_id,database_file_size)
select ''' + @db_name + ''',name,fileid,str(convert(dec(15),sum(size))* 8192 / 1048576 *'+@projected_size+',10,2)
from sysfiles
group by fileid,name'
EXEC (@sqlstat + @sqlstat2)
SET @loop_count = @loop_count + 1
END
/*******************************************************************************
**SET THE TOTAL SIZE OF EACH DATABASE
*******************************************************************************/
UPDATE #capacity
SET total_database_size = (SELECT str(sum(cast(database_file_size as dec(15,2))),15,2) +'MB'
FROM #capacity2
WHERE #capacity2.database_name = #capacity.database_name)
FROM #capacity
/*******************************************************************************
**RETURN RESULTS TO SCREEN
*******************************************************************************/
SELECT database_name,total_database_size
FROM #capacity
ORDER BY database_name
SELECT database_name,database_file_name,database_file_size + 'MB' AS 'FILE SIZE'
FROM #capacity2
ORDER BY database_name
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SECOND SCRIPT
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/******************************************************************************
* File Name: dbo.usp_capacity_table_all
* File Desc: Will get total capactity of all databases tables on the server
* as well as various table sizes such as reserved size,primary
* key size, number of records etc.
* Database: MASTER
* Language: MS Transact-SQL
* Version: MS SQL 2000
*
* Tables:
* SYSDATABASE,LOCAL SYSFILES,SYSOBJECTS,SYSINDEXES
*
*
* Procedures: N/A
*
* Date: 9/09/2002
* Author: KILEY MILAKOVIC(MCDBA)
* Architect:
* Project Mgr: ******************
* Project ID: N/A
*
* Notes:
* 1. *****
*
* Special Comments/Warnings
* None
*
*******************************************************************************
* Procedure Name: usp_capacity_table_all
*
* Procedure Desc: Will get total capactity of all databases tables on the server
* as well as various table sizes such as reserved size,primary
* key size, number of records etc.
*
* Parameters:
* @projected_size = DEFAULT IS NULL, IF YOU WISH TO FUTURE PROJECT
* GIVE INT WILL TAKE CURRENT SIZE AND MULTIPLY BY
* GIVEN AMOUNT OF YEARS.
*
* RETURNs: N/A
*
* Notes:
* None
*
*******************************************************************************
* Version: xxxx Author: xxxxxxxxxxxxxxxxxxx
* Date: xx/xx/xxxx
*******************************************************************************
* Description of Requests:
* 1.
*
* Description of Modifications:
* 1.
*
* Special Comments:
* None
*
* Other modules changed with this request:
* None
*******************************************************************************/
ALTER PROCEDURE usp_capacity_table_all @projected_size nvarchar(2)
AS
declare @db_count bigint,
@loop_count bigint,
@sqlstat varchar(7000),
@sqlstat2 varchar(7000),
@sqlstat3 varchar(7000),
@sqlstat4 varchar(7000),
@sqlstat5 varchar(7000),
@db_name varchar(150)
CREATE TABLE #tablestats
(
database_name varchar(150),
table_name varchar(700),
table_id bigint,
data_size nvarchar(38),
reserved_size nvarchar(38),
unused_size nvarchar(38),
index_size nvarchar(38),
primary_key_size nvarchar(38),
number_rows_table bigint
)
CREATE TABLE #alltablestats
(
database_name varchar(150),
table_name varchar(700),
table_id bigint,
data_size nvarchar(15),
reserved_size nvarchar(15),
unused_size nvarchar(15),
index_size nvarchar(15),
primary_key_size nvarchar(15),
number_rows_table bigint
)
CREATE TABLE #tabpage
(
database_name varchar(150),
table_name varchar(700),
table_id bigint,
page_size dec(30,0),
used_size dec(30,0),
index_size dec(30,0)
)
CREATE TABLE #pkhold
(
pk_id bigint,
pk_name varchar(400),
parent_id bigint,
pk_size nvarchar(15)
)
CREATE TABLE #capacity
(
tb_id bigint identity,
database_name varchar(150),
database_id bigint
)
IF @projected_size IS null or @projected_size = '0'
SET @projected_size = '1'
SET @loop_count = 1
/*****************************************************************************
***get database names on server ***
******************************************************************************/
INSERT INTO #capacity(database_name,database_id)
SELECT DISTINCT name,dbid
FROM sysdatabases
SELECT @db_count = count(*)
FROM #capacity
WHILE @loop_count <= @db_count
BEGIN
SELECT @db_name = database_name
FROM #capacity
WHERE tb_id = @loop_count
/*****************************************************************************
***get table name,id, and data size ***
******************************************************************************/
SET @sqlstat = 'use ' + @db_name
SET @sqlstat2 = ' insert into #tablestats(database_name,table_name,table_id)
SELECT '''+ @db_name + ''',name, ID FROM SYSOBJECTS WHERE XTYPE =''U'''
EXEC (@sqlstat + @sqlstat2)
SET @sqlstat2 = ' insert into #tabpage(database_name,table_name,table_id,page_size,used_size)
select distinct database_name,table_name, table_id, ''page size'' = (SELECT isnull(SUM(DPAGES),0)
FROM SYSINDEXES
WHERE SYSINDEXES.INDID < 2 AND
SYSINDEXES.ID = #tablestats.table_id),'
SET @sqlstat3 = '''used size'' = (SELECT ISNULL(SUM(USED),0)
FROM SYSINDEXES
WHERE SYSINDEXES.INDID = 255 AND
SYSINDEXES.ID = #tablestats.table_id)
from #tablestats '
EXEC (@sqlstat + @sqlstat2+@sqlstat3)
SET @sqlstat2 = ' update #tablestats set data_size = (select LTRIM(STR((page_size + used_size ) '
SET @sqlstat3 = '* 8192 / 1024.*'+@projected_size+',15,0) + '' '' + ''KB'') from #tabpage '
SET @sqlstat4 = 'where #tabpage.table_id = #tablestats.table_id and #tabpage.table_name = #tablestats.table_name) from #tablestats '
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get reserved size ***
******************************************************************************/
SET @sqlstat2 = ' update #tablestats set reserved_size = (SELECT LTRIM(STR(cast(SUM(RESERVED) as bigint) '
SET @sqlstat3 = ' * 8192 / 1024.*'+@projected_size+',300,0) + '' '' + ''KB'') FROM SYSINDEXES '
SET @sqlstat4 = ' WHERE SYSINDEXES.INDID IN (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats '
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get unused size ***
******************************************************************************/
SET @sqlstat2 = ' update #tablestats set unused_size = (SELECT LTRIM(STR((cast(SUM(RESERVED) as bigint)- SUM(USED)) '
SET @sqlstat3 = ' * 8192 / 1024.*'+@projected_size+',15,0) + '' '' + ''KB'') FROM SYSINDEXES '
SET @sqlstat4 = ' WHERE SYSINDEXES.INDID IN (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats '
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get index size ***
******************************************************************************/
DELETE
FROM #tabpage
SET @sqlstat2 = ' insert into #tabpage(table_id,page_size,used_size,index_size)select table_id, '
SET @sqlstat3 = ' ''page size'' = (SELECT SUM(DPAGES) FROM SYSINDEXES WHERE SYSINDEXES.INDID < 2 AND SYSINDEXES.ID = #tablestats.table_id), '
SET @sqlstat4 = ' ''used size'' = (SELECT SUM(convert(dec(15),USED)) FROM SYSINDEXES WHERE SYSINDEXES.INDID in (255) AND SYSINDEXES.ID = #tablestats.table_id), '
SET @sqlstat5 = ' ''index_size'' = (SELECT SUM(convert(dec(15),USED))FROM SYSINDEXES WHERE SYSINDEXES.INDID in (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats '
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4+@sqlstat5)
SET @sqlstat2 = ' update #tablestats set INDEX_size = (SELECT LTRIM(STR((index_SIZE - (PAGE_SIZE + ISNULL(USED_SIZE,0))) '
SET @sqlstat3 = ' * 8192 / 1024.*'+@projected_size+',15,0) + '' '' + ''KB'') FROM #TABPAGE '
SET @sqlstat4 = 'WHERE #tabPAGE.table_id = #tablestats.table_id) from #tablestats'
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get PK size ***
******************************************************************************/
SET @sqlstat2 = ' INSERT INTO #PKHOLD(PK_ID,PK_NAME,PARENT_ID) SELECT SO.ID, '
SET @sqlstat3 = ' SO.NAME,SO.PARENT_OBJ FROM SYSOBJECTS SO INNER JOIN #tablestats TS ON '
SET @sqlstat4 = ' SO.PARENT_OBJ = TS.TABLE_ID WHERE SO.XTYPE = ''PK'''
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
UPDATE #tablestats
SET primary_key_size = index_size
FROM #PKHOLD pk INNER JOIN #tablestats tb
ON pk.PARENT_ID = tb.table_id
/*****************************************************************************
***get number rows ***
******************************************************************************/
SET @sqlstat2 = ' update #tablestats set number_rows_table = rows*'+@projected_size
SET @sqlstat3 = ' from sysindexes si inner join #tablestats tb on si.id = tb.table_id '
SET @sqlstat4 = ' where si.indid < 2 '
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
INSERT INTO #alltablestats(database_name,table_name,table_id,data_size,
reserved_size,unused_size,index_size,
primary_key_size,number_rows_table)
SELECT database_name,table_name,table_id,data_size,
reserved_size,unused_size,index_size,
primary_key_size,number_rows_table
FROM #tablestats
ORDER BY database_name
DELETE FROM #tablestats
DELETE FROM #tabpage
DELETE FROM #pkhold
SET @loop_count = @loop_count + 1
END
/*****************************************************************************
***return results to the screen ***
******************************************************************************/
SELECT database_name,table_name,data_size,
reserved_size,unused_size,index_size,
primary_key_size,number_rows_table
FROM #alltablestats
ORDER BY DATABASE_NAME,TABLE_NAME
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
