Table Information View -- No Cursors!

  • Comments posted to this topic are about the item Table Information View -- No Cursors!

    [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]

  • Thanks for your unique point of view - it works like a charm ... will be added to my bag of "tricks"

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • This is what I use, however it would need to be modified to insert into a table if you wanted to select against it.

    sp_msforeachdb 'use ?;exec sp_spaceused'

    cheers,

    Andrew

  • bitbucket (4/8/2008)


    Thanks for your unique point of view - it works like a charm ... will be added to my bag of "tricks"

    Thanks for the feedback Bit.

    [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]

  • wavesmash (4/8/2008)


    This is what I use, however it would need to be modified to insert into a table if you wanted to select against it.

    sp_msforeachdb 'use ?;exec sp_spaceused'

    cheers,

    Andrew

    That's how I used to do it too Andrew, and that is actually the reason why I wrote this View. Besides being undocumented, sp_msForEachDB encapsulates a cursor to accomplish its magic, which carries along with it all of the limitations and problems of a cursor.

    I wanted something that could be used anywhere in SQL Server and that means a View. So I did essentially what you suggest: I looked at how sp_spaceused works and then extended it to a Select in order to build the View.

    [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]

  • I always like a query that gets down and dirty with system tables ;)! Nice. But I'm not sure occasionally cursoring through a few hundred tables is really that much of a chore for SQL Server! It's the counting that takes the time! Still, it's always nice to complete these challenges we set ourselves!

  • [font="Arial Narrow"]😛[/font]

    hello how r u dudes

    i want to know abt the normalisations

  • Nice script Barry. Is there one that will work on 2000?

  • I believe that this can be made to work on SQL 2000, Jack. But I would need some time on one of the few SQL 2000 systems that I still have access to, to work it out...

    [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]

  • bitbucket (4/8/2008)


    Thanks for your unique point of view - it works like a charm ... will be added to my bag of "tricks"

    Very nice, I too will add it to my toolkit. Especially love getting the schema details. I tried writing one to work with AdventureWorks which has multiple schemas, and got one to work, but no where near as elegant as your script.

    Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

  • Thanks, Gaby. Yeah, schema are the missing piece in a lot of the nicer scripts and tools for SQL Server. things changed so much from 2000 to 2005 that it's hard to come up with something that works in both but still has the additional info that I typically want in 2005. So on this one I decided to focus on getting that additional information.

    [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]

  • [font="Verdana"]The subqueries where a bit complicated for my liking, so I took your code and pulled out the subqueries into CTEs. Also, I have had indexes turn up with -ve numbers, so I don't like relying on the index_id being < 2 (I use an explicit (0, 1). That may be a hang over from statistics in SQL Server 2000, but I figure it's better safe than sorry.

    Runs a wee bit quicker this way in my testing. Anyway, the ideas are still yours. Here's the code variation.

    with

    spart as (

    select object_id,

    sum([rows]) as [RowCount]

    from sys.partitions

    where index_id in (0, 1)

    group by

    object_id

    ),

    sz as (

    select i.object_id,

    cast(round(

    cast(v.low as decimal(36,4)) *

    sum(

    case when a.type <> 1 then 0

    when p.index_id in (0, 1) then a.used_pages - a.data_pages

    else a.used_pages

    end)

    / 1024.00,

    0)

    as int) as [IndexKB],

    cast(round(

    cast(v.low as decimal(36,4)) *

    sum(

    case when a.type <> 1 then a.used_pages

    when p.index_id in (0, 1) then a.data_pages

    else 0

    end)

    / 1024.00,

    0)

    as int) as [DataKB]

    from sys.indexes as i

    inner join

    sys.partitions as p

    on p.object_id = i.object_id and

    p.index_id = i.index_id

    inner join

    sys.allocation_units as a

    on a.container_id = p.partition_id

    inner join

    master.dbo.spt_values v

    on v.number = 1 and

    v.type = 'E'

    group by

    v.low,

    i.object_id

    )

    select schema_name(tbl.schema_id) as [Schema],

    tbl.Name as [Table],

    isnull(pr.name, schema_name(tbl.schema_id)) as [Owner],

    tbl.max_column_id_used as [Columns],

    cast(idx.index_id as bit) as [ClusteredIndex],

    isnull(spart.[RowCount], 0) as [RowCount],

    isnull(sz.[IndexKB], 0) as [IndexKB],

    isnull(sz.[DataKB], 0) as [DataKB],

    tbl.create_date,

    tbl.modify_date

    from sys.tables as tbl

    inner join

    sys.indexes as idx

    on idx.object_id = tbl.object_id and

    idx.index_id in (0, 1)

    left join

    sys.database_principals pr

    on pr.principal_id = tbl.principal_id

    left join

    spart

    on tbl.object_id = spart.object_id

    left join

    sz

    on tbl.object_id = sz.object_id;

    [/font]

  • Thanks, Bruce

    [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]

  • [font="Verdana"]Welcome. It's a good script, and modifying it was easy. 🙂

    I used to do this on SQL Server 2000, just by selecting from sysindexes with a script like the following snippet:

    select object_name(id) as [Table], Rows

    from sysindexes

    where indid in (0, 1) and object_name(id) not like 'sys%';

    You can do this with nolock on the table to see the rows building.

    However, I wanted to come up with something like that in SQL Server 2005, and had a bit of a play with it. Tracing the various connections between the system allocation tables turned into too much of a time waste at the time. But it turns out that you did the hard work for me!

    So again, a good script. Added to my toolbox.

    [/font]

  • Barry, a very nice script indeed and I have already added it to my bag of tricks. A question though: I see some tables in my database that looks like this "~TMPCLP156961". What are they and can I delete them?

    I would like to take your query further and add dependencies to it because I see a few tables that looks like they might have been created as a temporary table and never deleted and if I know the dependencies e.g. sp's, views etc then I can maybe (very carefully) delete them. I'd appreciate some help on this.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Viewing 15 posts - 1 through 15 (of 22 total)

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