Post reply

script to get all tables an sizes

  • December 13, 2008 at 8:44 am

    #912050

    When I run your script I'm getting this error:

    Server: Msg 195, Level 15, State 10, Line 1

    'SCHEMA_NAME' is not a recognized function name.

    Server: Msg 170, Level 15, State 1, Line 6

    Line 6: Incorrect syntax near ','.

    Server: Msg 170, Level 15, State 1, Line 10

    Line 10: Incorrect syntax near ','.

    Server: Msg 170, Level 15, State 1, Line 18

    Line 18: Incorrect syntax near ','.

    Server: Msg 170, Level 15, State 1, Line 26

    Line 26: Incorrect syntax near ','.

  • December 13, 2008 at 8:46 am

    #912052

    Ah, and what version of SQL server are we running on?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 13, 2008 at 9:02 am

    #912055

    I need be able to run on 2000 and 2005, but I just run it on 2000

  • December 13, 2008 at 9:32 am

    #912064

    Yes, this is a SQL Server 2005/2008 script, because as you will notice at the top of the page, this is a SQL Server 2005 forum and this is the first time that you have mentioned SQL Server 2000.

    You can search the scripts on this site for examples that work on SQL Server 2000.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 13, 2008 at 9:37 am

    #912065

    I just run it on 2005 and get the same exactly error

  • December 13, 2008 at 9:38 am

    #912066

    I need be able to run on 2000 and 2005, but I just run it on 2000

    Since this is a "2005" forum, it would have been nice to know that much earlier in the thread... 😉

    Thank goodness for "legacy views" in 2005... the following will run in either SQL Server 2000 or 2005 once you've made the minor change required as cited in NOTE 2 of the comments in the code... do read the comments in the code BEFORE you try running it... they explain a lot...

    /**********************************************************************************************************************

    Purpose:

    Returns a single result set similar to sp_Space used for all user tables at once.

    Notes:

    1. May be used as a view, stored procedure, or table-valued funtion.

    2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.

    Revision History:

    Rev 00 - 22 Jan 2007 - Jeff Moden

    - Initital creation for SQL Server 2000

    Rev 01 - 11 Mar 2007 - Jeff Moden

    - Add automatic page size determination for future compliance

    Rev 02 - 05 Jan 2008 - Jeff Moden

    - Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name

    **********************************************************************************************************************/

    --===== Ensure that all row counts, etc is up do snuff

    -- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should

    -- execute the command below prior to retrieving from the view or UDF.

    DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS

    --===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more

    SELECT DBName = DB_NAME(),

    --SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000

    SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005

    TableName = so.Name,

    TableID = so.ID,

    MinRowSize = MIN(si.MinLen),

    MaxRowSize = MAX(si.XMaxLen),

    ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,

    DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

    + SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

    IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

    UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,

    Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),

    RowModCtr = MIN(si.RowModCtr),

    HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),

    HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)

    FROM dbo.SysObjects so,

    dbo.SysIndexes si,

    (--Derived table finds page size in KB according to system type

    SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte

    FROM Master.dbo.spt_Values

    WHERE Number = 1 --Identifies the primary row for the given type

    AND Type = 'E' --Identifies row for system type

    ) pkb

    WHERE si.ID = so.ID

    AND si.IndID IN (0, --Table w/o Text or Image Data

    1,

    255) --Table w/ Text or Image Data

    AND so.XType = 'U' --User Tables

    AND PERMISSIONS(so.ID) <> 0

    GROUP BY so.Name,

    so.UID,

    so.ID,

    pkb.PageKB

    ORDER BY ReservedKB DESC

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 13, 2008 at 9:47 am

    #912069

    Damned smiley faces!!! Make sure that you change all the smiley faces in the code above with right parenthesis...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 13, 2008 at 11:41 am

    #912075

    neneco56: I do not know why this is not working for you, you should only get that error on SQL 2000 systems. Even SQL 2005 databases in compatibility mode still work fine with this script. And I know that myself and hundreds of other folks have used it successfully on SQL 2005 servers without out error.

    For now, I would suggest trying Jeff's routine (thanks Jeff! I was sure that someone would have a compatible version).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 13, 2008 at 11:54 am

    #912080

    I'm not sure why your good script isn't working in 2k5 for neneco56 either... works fine for me.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 13, 2008 at 12:05 pm

    #912081

    maybe need a service pack??

  • December 13, 2008 at 12:10 pm

    #912084

    Could be... you should be on at least SP2.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 13, 2008 at 12:29 pm

    #912087

    That error only ever happens on SQL 2000 server (and lower), So I would have to say that the query must actually be going to (or through) a SQL 2000 server.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply