Table sizes for all databases

  • 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

    username=@Gebruikernaam

    ,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()

     

  • 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

  • Thanks for the suggestion, I'll try that one out.

  • 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

    username=@UserName

    ,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