December 14, 2006 at 7:54 am
Hello, I wanted to create a script to find out how much the databases grows and which tables are growing in sql serve 2000. My scripts has gotten offtrack since it only shows tables of 1 database (db_name won't change). Is there a premade script?
Script so far
drop table #temp
drop table #temp2
drop table #Databases
DECLARE @Tabelnaam nvarchar(128)
DECLARE @Gebruikernaam nvarchar(128)
DECLARE @Samen nvarchar(128)
DECLARE @Databasenaam nvarchar(128)
CREATE TABLE #Databases
(
Databasenaam nvarchar(128)
)
CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
username varchar(128),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2),
dbname varchar(128)
)
CREATE TABLE #temp2(
rec_id int IDENTITY (1, 1),
username varchar(128),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2),
dbname varchar(128)
)
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))
declare @inaccessible nvarchar(12)
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
insert into #Databases
(Databasenaam)
select name from master.dbo.sysdatabases d
where (d.status & @inaccessible = 0)
and ((DATABASEPROPERTY(d.name, 'issingleuser') = 0 and (has_dbaccess(d.name) = 1))
or ( DATABASEPROPERTY(d.name, 'issingleuser') = 1 and not exists
(select * from master.dbo.sysprocesses p where dbid = d.dbid and p.spid <> @@spid)))
select *
from #Databases
DECLARE TESTDB CURSOR
FOR SELECT Databasenaam
FROM #Databases
order by Databasenaam
OPEN TESTDB
FETCH NEXT FROM TESTDB INTO @Databasenaam
WHILE @@fetch_status=0
BEGIN
DECLARE @CMD55 NVARCHAR(400)
SET @CMD55='USE '+@Databasenaam + ' EXECUTE statistiek.dbo.TEST55'
--SELECT @CMD55
/*current database doesn't change?*/
execute master.dbo.sp_executesql @CMD55
FETCH NEXT FROM TESTDB INTO @Databasenaam
END
CLOSE TESTDB
DEALLOCATE TESTDB
SELECT *
fROM #temp2
/*aiding stored proc*/
CREATE PROCEDURE TEST55
AS
SET NOCOUNT ON
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))
declare @inaccessible nvarchar(12)
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
DECLARE @Tabelnaam nvarchar(128)
DECLARE @Gebruikernaam nvarchar(128)
DECLARE @Samen nvarchar(128)
DECLARE @Databasenaam nvarchar(128)
insert into #temp
(username,table_name,dbname)
select user_name(uid),object_name(id),db_name()
from dbo.sysobjects o
where OBJECTPROPERTY(o.id,N'IsUserTable') = 1
and o.category & @mscat = 0
order by user_name(uid),object_name(id)
DECLARE TEST CURSOR
FOR SELECT username,table_name
from #temp
order by username,table_name
OPEN TEST
FETCH NEXT from TEST INTO @Gebruikernaam,@Tabelnaam
while @@fetch_status=0
begin
SET @Samen=@Gebruikernaam+'.'+@Tabelnaam
--SELECT @Tabelnaam
INSERT INTO #temp2 (nbr_of_rows, data_space, index_space) EXECUTE sp_MStablespace @Samen
update #temp2 set
,table_name=@Tabelnaam
,dbname=db_name()
where rec_id = (select max(rec_id) from #temp2)
FETCH NEXT from TEST INTO @Gebruikernaam,@Tabelnaam
END
CLOSE TEST
DEALLOCATE TEST
select db_name()
December 14, 2006 at 8:42 am
Jo
Just an idea, this...
You have a stored procedure called TEST5 in a database called Statistiek, right? I've a feeling that EXEC Statistiek.dbo.TEST5 will execute that proc against Statistiek rather than the database whose context you have just changed to. Try creating the proc in the master database as sp_test5 and then calling 'EXEC ' + @Databasenaam + '.dbo.sp_test5'.
John
December 14, 2006 at 12:38 pm
Thanks for the suggestion, I'll try that one out.
December 15, 2006 at 3:53 am
Thanks again, it worked out.
Script:
DECLARE @Tablename nvarchar(128)
DECLARE @UserName nvarchar(128)
DECLARE @ConcatName nvarchar(128)
DECLARE @DatabaseName nvarchar(128)
CREATE TABLE #Databases
(
Databasenaam nvarchar(128)
)
CREATE TABLE #temp2(
rec_id int IDENTITY (1, 1),
username varchar(128),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2),
dbname varchar(128)
)
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))
declare @inaccessible nvarchar(12)
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
/*insert valid user databases*/
insert into #Databases
(Databasenaam)
select name from master.dbo.sysdatabases d
where (
(d.status & @inaccessible = 0) and ((DATABASEPROPERTY(d.name, 'issingleuser') = 0 and (has_dbaccess(d.name) = 1))
or ( DATABASEPROPERTY(d.name, 'issingleuser') = 1 and not exists (select * from master.dbo.sysprocesses p where dbid = d.dbid and p.spid <> @@spid)))
)
and dbid>4
DECLARE TESTDB CURSOR
FOR SELECT Databasenaam
FROM #Databases
order by Databasenaam
OPEN TESTDB
FETCH NEXT FROM TESTDB INTO @DatabaseName
WHILE @@fetch_status=0
BEGIN
/*Thanks to John Mitchell for the suggestion to put the procedure in master and naming it sp_*/
/*db_name changes now correctly*/
DECLARE @CMD55 NVARCHAR(400)
SET @CMD55='USE '+@DatabaseName + ' EXECUTE '+@DatabaseName+'.dbo.SP_DATABASE_TABLE_SIZE'
execute master.dbo.sp_executesql @CMD55
FETCH NEXT FROM TESTDB INTO @DatabaseName
END
CLOSE TESTDB
DEALLOCATE TESTDB
SELECT *
fROM #temp2
drop table #temp2
drop table #Databases
CREATE PROCEDURE dbo.SP_DATABASE_TABLE_SIZE
AS
SET NOCOUNT ON
/*
--#temp2 being supplied by caller
--place in master and start name with sp_
*/
CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
username varchar(128),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2),
dbname varchar(128)
)
/*
CREATE TABLE #temp2(
rec_id int IDENTITY (1, 1),
username varchar(128),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2),
dbname varchar(128)
)
*/
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))
declare @inaccessible nvarchar(12)
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
DECLARE @TableName nvarchar(128)
DECLARE @UserName nvarchar(128)
DECLARE @ConcatName nvarchar(128)
DECLARE @DatabaseName nvarchar(128)
insert into #temp
(username,table_name,dbname)
select user_name(uid),object_name(id),db_name()
from dbo.sysobjects o
where OBJECTPROPERTY(o.id,N'IsUserTable') = 1
and o.category & @mscat = 0
order by user_name(uid),object_name(id)
DECLARE TABLECURSOR CURSOR
FOR SELECT username,table_name
from #temp
where dbname=db_name()
order by username,table_name
OPEN TABLECURSOR
FETCH NEXT from TABLECURSOR INTO @UserName,@TableName
while @@fetch_status=0
begin
SET @ConcatName=quotename(@UserName)+'.'+quotename(@TableName)
--SELECT @TableName
INSERT INTO #temp2 (nbr_of_rows, data_space, index_space) EXECUTE sp_MStablespace @ConcatName
update #temp2 set
,table_name=@TableName
,dbname=db_name()
where rec_id = (select max(rec_id) from #temp2)
-- Set the total_size and total database size fields
UPDATE #temp2
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp2 where dbname=db_name())
where dbname=db_name()
-- Set the percent of the total database size
UPDATE #temp2
SET percent_of_db =
CASE
WHEN db_size=0 THEN 0
ELSE (total_size/db_size) * 100
END
FETCH NEXT from TABLECURSOR INTO @UserName,@TableName
END
CLOSE TABLECURSOR
DEALLOCATE TABLECURSOR
drop table #temp
--select db_name()
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply