sysobjects shows table not in database

  • I am new to SQL Server 2008 R2. However, I do have many years as a DBA, but for other engines.

    I am running across an anomaly in running a stored procedure. It is called sp_defragment_indexes,

    which was installed when I arrived here. This SP works on some servers, including production, just

    fine, but is failing with an ususual name in the error message on test servers:

    Cannot find a table or object with the name "\ingres.pm_shopop_tbl". Check the system catalog.

    [SQLSTATE 42S02] (Error 2501). The step failed.

    Notice the "\" in the name listed in the error message. The message is reasonable - there is no

    such table in the database.

    In trying to track down the issue, I performed these tests:

    use [test]

    go

    dbcc showcontig ('dbo.iso_currency_tbl')

    go

    select * from sysobjects

    wHERE type ='U'

    and name = 'iso_currency_tbl'

    go

    dbcc result:

    Msg 2501, Level 16, State 45, Line 1

    Cannot find a table or object with the name "dbo.iso_currency_tbl". Check the system catalog.

    Using the table name without the "dbo" returns the same message.

    However, the select returns a record!

    (1 row(s) affected)

    iso_currency_tbl803063850U 5 etc.

    This table is NOT in the tables list in the database!

    In the procedure, there is this SQL:

    SELECT convert(varchar,so.id)

    FROM sysobjects so

    JOIN sysindexes si

    ON so.id = si.id

    WHERE so.type ='U'

    AND si.indid < 2

    AND si.rows > 0

    Taking this query apart:

    select * from sysobjects where name = 'iso_currency_tbl'

    Record found!

    select * from sysindexes where id = 803063850

    Two records found, indid = 1 and indid = 2.

    Why does the sysobjects table show iso_currency_tbl, but the table list does not and dbcc showcongfig

    does not find it either? Can sysobjects get out of sync with the real objects that exist?

    Any idea why there is a "\" in the message from the SP?

  • i think the table might be in another schema, but you are assuming it's in the dbo schema, since that's what is your default.

    try this instead...what schema does it really belong to?

    select

    SCHEMA_NAME(SCHEMA_ID),

    name,

    'DBCC showcontig (''' + quotename(SCHEMA_NAME(SCHEMA_ID)) + '.' + quotename(name)+''')' As cmd,

    * from sys.objects

    wHERE type ='U'

    and name = 'iso_currency_tbl'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks. I did have the wrong owner (it is "Adage"). Running the showcontig with that dbo works.

    That solves that mystery. However, the error remains :-(.

    I also just noticed that I had a typo in my original post!

    The error message I am getting from the stored procedure is:

    Cannot find a table or object with the name "\ingres.iso_currency_tbl". Check the system catalog. [SQLSTATE 42S02] (Error 2501). The step failed.

    The other message in the original post came from a different server. Notice, however, that there is a "\" in both messages, and only the table name in the messages changed.

  • well, i think the issue's in the procedure you are talking about ...i don't think it's schema aware;

    i wouldn't take the error message from the scheduled job literally(that a slash exists in some object name)

    if you post the procedure, we can help make it schema-aware , which i'm sure will make the error go away.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • You found the issue. The SP was using a call to user_name(so.uid) to set the object's schema name in the dbcc command, where the uid is the value from the sysobjects table. In this case, this call returned "\ingress"! Certainly not the real schema name. Good catch!

    I did not write this SP - it was written a few years ago. It indeed needs to be made "schema" aware (properly!).

    Here it is:

    SET NOCOUNT ON

    DECLARE @tablename VARCHAR (128)

    DECLARE @execstr VARCHAR (255)

    DECLARE @objectid INT

    DECLARE @objectowner VARCHAR(255)

    DECLARE @indexid INT

    DECLARE @frag DECIMAL

    DECLARE @indexname CHAR(255)

    DECLARE @dbname sysname

    DECLARE @tableid INT

    DECLARE @tableidchar VARCHAR(255)

    --check this is being run in a user database

    SELECT @dbname = db_name()

    IF @dbname IN ('master', 'msdb', 'model', 'tempdb')

    BEGIN

    PRINT 'This procedure should not be run in system databases.'

    RETURN

    END

    --begin Stage 1: checking fragmentation

    -- Declare cursor

    DECLARE tables CURSOR FOR

    SELECT convert(varchar,so.id)

    FROM sysobjects so

    JOIN sysindexes si

    ON so.id = si.id

    WHERE so.type ='U'

    AND si.indid < 2

    AND si.rows > 0

    -- Create the temporary table to hold fragmentation information

    CREATE TABLE #fraglist (

    ObjectName CHAR (255),

    ObjectId INT,

    IndexName CHAR (255),

    IndexId INT,

    Lvl INT,

    CountPages INT,

    CountRows INT,

    MinRecSize INT,

    MaxRecSize INT,

    AvgRecSize INT,

    ForRecCount INT,

    Extents INT,

    ExtentSwitches INT,

    AvgFreeBytes INT,

    AvgPageDensity INT,

    ScanDensity DECIMAL,

    BestCount INT,

    ActualCount INT,

    LogicalFrag DECIMAL,

    ExtentFrag DECIMAL)

    -- Open the cursor

    OPEN tables

    -- Loop through all the tables in the database running dbcc showcontig on each one

    FETCH NEXT

    FROM tables

    INTO @tableidchar

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Do the showcontig of all indexes of the table

    INSERT INTO #fraglist

    EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') with TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

    FETCH NEXT

    FROM tables

    INTO @tableidchar

    END

    -- Close and deallocate the cursor

    CLOSE tables

    DEALLOCATE tables

    -- Report the ouput of showcontig for results checking

    SELECT * FROM #fraglist

    -- Begin Stage 2: (defrag) declare cursor for list of indexes to be defragged

    DECLARE indexes CURSOR FOR

    SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ExtentFrag

    FROM #fraglist f

    JOIN sysobjects so ON f.ObjectId=so.id

    WHERE ScanDensity <= @maxfrag

    AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

    -- Write to output start time for information purposes

    SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

    -- Open the cursor

    OPEN indexes

    -- Loop through the indexes

    FETCH NEXT

    FROM indexes

    INTO @tablename, @objectowner, @objectid, @indexname, @frag

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET QUOTED_IDENTIFIER ON

    SELECT @execstr = 'DBCC DBREINDEX (' + "'" +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + "'" +

    ', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'

    SELECT 'Now executing: '

    SELECT(@execstr)

    EXEC (@execstr)

    SET QUOTED_IDENTIFIER OFF

    FETCH NEXT

    FROM indexes

    INTO @tablename, @objectowner, @objectid, @indexname, @frag

    END

    -- Close and deallocate the cursor

    CLOSE indexes

    DEALLOCATE indexes

    -- Report on finish time for information purposes

    SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

    -- Delete the temporary table

    DROP TABLE #fraglist

    GO

  • ok for me the fix is easy;

    there's a much better, award winning, peer reviewed and rather famous script to help handle things like reindexing.

    it's been deployed by more businesses, on bigger servers and databases than i will ever have the pleasure of working on.

    replace your current job with one using this scripts instead.

    http://ola.hallengren.com/

    (one of the best tools in my toolbox)

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Again, my thanks! I will download your suggested scripts and see about installing them.

    In the meantime, I found a fix (work-around) to the sp_defragment_indexes script in another older posting:

    "The biggest problem with using this procedure in SQL 2005 is that the table owner is not necessarily the same as the schema that the table belongs to. You can't use the sysobjects uid value like this anymore. You can replace "ObjectOwner = user_name(so.uid)" with "ObjectOwner = object_schema_name(id)" "

    Making that change resolved the issue.

    Thanks again!!

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

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