SQL Server Performance

Size on all databases

Discussion in 'Contribute Your SQL Server Scripts' started by Anonymous, Aug 1, 2006.

  1. Anonymous New Member

    exec sp_msforeachdb 'use ? exec sp_spaceused'

    next2none is what others feel about the maverick
  2. Luis Martin Moderator

    Thanks Sidh.

    Kindly we expect more complex scripts in this forum. You will find a lot of post with your suggestion.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. Anonymous New Member

    Sure Luis il do that... and shall come up with suggestions as well

    Regards
    Sidh

    next2none is what others feel about the maverick
  4. satya Moderator

    Sidh
    I appreciate your trials to post the relevant information, but please adhere to the text
    quote:If you have written an original script that you think may be of use to other SQL Server DBAS or developers, please post them here. Please only post original scripts, not scripts you have found elsewhere.


    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  5. Madhivanan Moderator

    If someone finds very useful script, then can it be posted here? (only referral Link)

    Madhivanan

    Failing to plan is Planning to fail
  6. FrankKalis Moderator

  7. satya Moderator

    Sidh
    Don't get us wrong, in order to keep up the forum policies all the posts will be monitored, please do adhere to the forum policies.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  8. harish_may8h New Member

    For SQL 2005
    BEGIN
    try DECLARE
    @table_name VARCHAR(500) ; DECLARE
    @schema_name VARCHAR(500) ; DECLARE @tab1 TABLE( tablename
    VARCHAR (500) collate database_default ,
    schemaname VARCHAR(500) collate database_default );DECLARE @temp_table TABLE ( tablename
    sysname ,
    row_count INT ,
    reserved VARCHAR(50) collate database_default ,
    data VARCHAR(50) collate database_default ,
    index_size VARCHAR(50) collate database_default ,
    unused VARCHAR(50) collate database_default );INSERT
    INTO @tab1 SELECT
    t1.name ,
    t2.name FROM
    sys.tables t1 INNER
    JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id ); DECLARE
    c1 CURSOR FOR SELECT
    t2.name + '.' + t1.name FROM
    sys.tables t1 INNER
    JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id ); OPEN
    c1; FETCH
    NEXT FROM c1 INTO @table_name; WHILE
    @@FETCH_STATUS = 0 BEGIN
    SET @table_name = REPLACE(@table_name, '[',''); SET @table_name = REPLACE(@table_name, ']','');
    -- make sure the object exists before calling sp_spacedused IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
    BEGIN INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ; END
    FETCH NEXT FROM c1 INTO @table_name; END
    ; CLOSE
    c1; DEALLOCATE
    c1; SELECT
    t1.* ,
    t2.schemaname FROM
    @temp_table t1 INNER
    JOIN @tab1 t2 ON (t1.tablename = t2.tablename ) ORDER
    BY schemaname,tablename; END
    try BEGIN
    catch SELECT
    -100 AS l1 ,
    ERROR_NUMBER() AS tablename ,
    ERROR_SEVERITY() AS row_count ,
    ERROR_STATE() AS reserved ,
    ERROR_MESSAGE() AS data ,
    1 AS index_size, 1 AS unused, 1 AS schemaname END
    catch
    FOR SQL 2000
    DECLARE @table_name VARCHAR(500)
    DECLARE @schema_name VARCHAR(500)
    DECLARE @tab1 TABLE(
    tablename VARCHAR (500) collate database_default
    ,schemaname VARCHAR(500) collate database_default
    )

    CREATE TABLE #temp_Table (
    tablename sysname
    ,row_count INT
    ,reserved VARCHAR(50) collate database_default
    ,data VARCHAR(50) collate database_default
    ,index_size VARCHAR(50) collate database_default
    ,unused VARCHAR(50) collate database_default
    )

    INSERT INTO @tab1
    SELECT Table_Name, Table_Schema
    FROM information_schema.tables
    WHERE TABLE_TYPE = 'BASE TABLE'

    DECLARE c1 CURSOR FOR
    SELECT
    Table_Schema + '.' + Table_Name
    FROM information_schema.tables t1
    WHERE TABLE_TYPE = 'BASE TABLE'

    OPEN c1
    FETCH NEXT FROM c1 INTO @table_name
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET
    @table_name = REPLACE(@table_name, '[','');
    SET @table_name = REPLACE(@table_name, ']','');

    -- make sure the object exists before calling sp_spacedused
    IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
    BEGIN
    INSERT INTO
    #temp_Table EXEC sp_spaceused @table_name, false;
    END

    FETCH
    NEXT FROM c1 INTO @table_name
    END
    CLOSE
    c1
    DEALLOCATE c1

    SELECT t1.*
    ,
    t2.schemaname
    FROM #temp_Table t1
    INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
    ORDER BY schemaname,t1.tablename;

    DROP TABLE #temp_Table
  9. FrankKalis Moderator

Share This Page