May 18, 2010 at 2:05 am
Hi friends,
My basic requirement is to collect database table growth in all databases
here is my script
print ' ******************************************** Database Table Growth Report *********************************** '
Declare @dbname varchar(100)
----- Run This command at every database to get to 5 tables details -----
declare c1 cursor for select name from master..sysdatabases
open c1
fetch c1 into @dbname
print @dbname
while @@fetch_status = 0
begin
SET NOCOUNT ON
CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
table_name varchar(128) NULL,
nbr_of_rows int NULL,
data_space decimal(15,2) NULL,
index_space decimal(15,2) NULL,
total_size decimal(15,2) NULL,
total_sizeg decimal(15,2) NULL,
percent_of_db decimal(15,2) NULL,
db_size decimal(15,2) NULL,
db_sizeg decimal(15,2) NULL)
-- Get all tables, names, and sizes
EXEC sp_MSforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_MStablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
--Exec sp_MSforeachdb
/*EXEC sp_MSforeachtable
@command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_MStablespace '?'",
@command2="update #temp set table_name = '?'
from sysdatabases
where rec_id = (select max(rec_id) from #temp)" */
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)
-- Convert total_size to GIG
UPDATE #temp
SET total_sizeg = total_size
-- Convert db_size to GIG
UPDATE #temp
SET db_sizeg = db_size
-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100
-- Get the data
SELECT top (10)@@ServerName as ServerName, db_name() as DatabaseName, table_name as TableName, nbr_of_rows as [Rows],
data_space as DataKB, index_space as IndexKB,
total_size as TotalSizeKB,
total_sizeg as TotalSizeGB,
percent_of_db as [%Usage],
db_size as DBSizeKB, db_sizeg as DBSizeGB,
(convert(varchar(8),getdate(),112)) as RunDate
FROM #temp
ORDER BY total_size DESC
fetch next from c1 into @dbname
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
end
close c1
deallocate c1
GO
-- Comment out the following line if you want to do further querying
my question is how can i use "use dbname" statment in this script?
Plz Help
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
May 18, 2010 at 3:58 am
hey frnds i got the ans
i have changed the script as
print ' ******************************************** Database Table Growth Report *********************************** '
Declare @dbname varchar(100)
declare @MyCommand nvarchar(1000)
----- Run This command at every database to get to 5 tables details -----
declare d1 cursor for select name from master..sysdatabases where dbid not in(1,2,3,4)
open d1
fetch d1 into @dbname
print @dbname
while @@fetch_status = 0
begin
SET NOCOUNT ON
CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
table_name varchar(128) NULL,
nbr_of_rows int NULL,
data_space decimal(30,8) NULL,
index_space decimal(30,8) NULL,
total_size decimal(30,8) NULL,
total_sizeg decimal(30,8) NULL,
percent_of_db decimal(15,8) NULL,
db_size decimal(30,8) NULL,
db_sizeg decimal(30,8) NULL)
-- Get all tables, names, and sizes
SET @MyCommand = 'USE [' + @dbname + '] EXEC sp_MSforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space)
exec sp_MStablespace ''?''",@command2="update #temp set table_name = ''?'' where rec_id = (select max(rec_id) from #temp)"'
EXECUTE(@MyCommand)
--EXEC sp_MSforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_MStablespace '?'",
--@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
--Exec sp_MSforeachdb
/*EXEC sp_MSforeachtable
@command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_MStablespace '?'",
@command2="update #temp set table_name = '?'
from sysdatabases
where rec_id = (select max(rec_id) from #temp)" */
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)
-- Convert total_size to GIG
UPDATE #temp
SET total_sizeg = total_size /(1024*1024)
-- Convert db_size to GIG
UPDATE #temp
SET db_sizeg = db_size /(1024*1024)
-- Set the percent of the total database size
Begin Try
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100
end try
begin catch
Print 'db_size is greater than Or equal to total_size OR Table sizes must be Zero'
end catch
-- Get the data
SELECT top (10)@@ServerName as ServerName, @dbname as DatabaseName, table_name as TableName, nbr_of_rows as [Rows],
data_space as DataKB, index_space as IndexKB,
total_size as TotalSizeKB,
total_sizeg as TotalSizeGB,
percent_of_db as [%Usage],
db_size as DBSizeKB, db_sizeg as DBSizeGB,
(convert(varchar(8),getdate(),112)) as RunDate
FROM #temp
ORDER BY total_size DESC
fetch next from d1 into @dbname
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
end
close d1
deallocate d1
GO
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply