sysobjects shows table not in database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » sysobjects shows table not in database

sysobjects shows table not in database

Rate Topic

Display Mode

Topic Options

Author

Message

thomas.horner

Posted Tuesday, October 16, 2012 1:38 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 2:01 PM
Points: 8, Visits: 33

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_tbl 803063850 U 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?

Post #1373498

Lowell

Posted Tuesday, October 16, 2012 1:44 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1373503

thomas.horner

Posted Tuesday, October 16, 2012 2:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 2:01 PM
Points: 8, Visits: 33

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.

Post #1373514

Lowell

Posted Tuesday, October 16, 2012 2:12 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1373518

thomas.horner

Posted Tuesday, October 16, 2012 2:43 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 2:01 PM
Points: 8, Visits: 33

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

Post #1373532

Lowell

Posted Tuesday, October 16, 2012 2:50 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1373535

thomas.horner

Posted Wednesday, October 17, 2012 8:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 2:01 PM
Points: 8, Visits: 33

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!!

Post #1373856

« Prev Topic | Next Topic »

Permissions