what are these objects prefixed with ''t''

  • use Northwind

    select * from sysindexes where name = 'tCategories'

    I am just using Northwind so that everyone can easily see,

    but run this query and you will see this in all databases

    select *

    from sysindexes

    where name = 't' + object_name( id )

     

  • I believe those are entries for tables that contain text or image data.

    Greg

    Greg

  • Aha! thank you very much Greg!

    for the benefit of others:

    create table PrefixCreator1(f1 text)

    create table PrefixCreator2(f1 image)

    create table PrefixCreator3(f1 text, f2 image)

    select *

    from sysindexes

    where name = 't' + object_name( id )

    http://msdn2.microsoft.com/en-us/library/aa174534(SQL.80).aspx

  • using a query to highlight which indexes are in need of a quick defrag etc which is based around

    DBCC SHOWCONTIG (Categories) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

    go

    the results include indexname = tCategories, yet if you try

    DBCC INDEXDEFRAG (Northwind, Categories, tCategories)

    GO

    it returns

    Server: Msg 2560, Level 16, State 1, Line 1

    Parameter 3 is incorrect for this DBCC statement.

    Are these t prefixed indexes "real" indexes in the sense they can be defragged/rebuilt etc using dbcc commands ?

    many thanks Si

  • The "t" prefixed "indexes" are not "real" indexes... they are merely notations to the server that the table contains image or text datatypes...

    --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)
    Intro to Tally Tables and Functions

  • Many thanks, Ive not got to work out the best way to exclude them when Im using the following syntax to determine which indexes need defraging

    CREATE TABLE #index

    (

    Table_Name varchar(255),

    Table_ID int,

    Index_Name varchar(255),

    Index_ID int,

    Index_Level int,

    Page_Count int,

    Row_Count int,

    Minimum_Record_Size int,

    Maximum_Record_Size int,

    Average_Record_Size int,

    Forwarded_Record_Count int,

    Extents int,

    Extent_Switches int,

    Average_Free_Bytes int,

    Average_Page_Density int,

    Scan_Density decimal,

    Best_Count int,

    Actual_Count int,

    Logical_Fragmentation decimal,

    Extent_Fragmentation decimal

    )

    -- declare and open a cursor on the list of user table names

    DECLARE table_cursor CURSOR LOCAL STATIC FORWARD_ONLY FOR

    SELECT TABLE_NAME

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE = 'BASE TABLE'

    AND TABLE_NAME 'dtproperties'

    OPEN table_cursor

    -- get index information for each table

    DECLARE @table_name varchar(128)

    FETCH NEXT FROM table_cursor INTO @table_name

    WHILE ( @@FETCH_STATUS = 0 )

    BEGIN

    -- use the DBCC command to get index fragmentation info

    INSERT

    INTO #index

    EXEC( 'DBCC SHOWCONTIG ( ' + @table_name + ' )

    WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )

    -- get the next table

    FETCH NEXT FROM table_cursor INTO @table_name

    END

    -- close and deallocate the cursor

    CLOSE table_cursor

    DEALLOCATE table_cursor

    -- output a result set of each index with its page count and logical

    -- fragmentation

    SELECT #index.Table_Name,

    #index.Index_Name,

    #index.Page_Count,

    #index.Logical_Fragmentation

    FROM #index

    --where #index.Logical_Fragmentation >30

    ORDER BY #index.Logical_Fragmentation DESC

    -- all ok

    RETURN( 0 )

    GO

  • Oh noo, no, no.... go look at Master.dbo.sp_SpaceUsed and "steal" code from that... do it in a set based manner so that you can create a view like an Information_Schema view.

    --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)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 7 (of 7 total)

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