sp_help can't see my tables

[D Bull]

I'm trying to use sp_help to get information on my tables. I can use
sp_help alone to get a list of objects (including user tables), but
when I pass a table name as an argument I get the following error
message:

exec sp_help

exec sp_help parcel

Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71
The object 'DGM_HILLSHADE2' does not exist in database 'raster'.

The table clearly exists, but sp_help fails to find it and return
info. This is the case in a couple of my databases. I'm connected as
'sa' so it seems to me it shouldn't be a permissions problem, right?

D Bull

[Erland Sommarskog]

D Bull (db****@yahoo.com) writes:

I'm trying to use sp_help to get information on my tables. I can use
sp_help alone to get a list of objects (including user tables), but
when I pass a table name as an argument I get the following error
message:

exec sp_help

exec sp_help parcel

Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71
The object 'DGM_HILLSHADE2' does not exist in database 'raster'.

The table clearly exists, but sp_help fails to find it and return
info. This is the case in a couple of my databases. I'm connected as
'sa' so it seems to me it shouldn't be a permissions problem, right?

But if the table is owned by another user than dbo, you need to specify
that user:

sp_help 'anotheruser.parcel'

However, message complains about another object that the one you give on
the command line, which seems a little funny.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Philip Yale]

db****@yahoo.com (D Bull) wrote in message news:<26**************************@posting.google. com>...

I'm trying to use sp_help to get information on my tables. I can use
sp_help alone to get a list of objects (including user tables), but
when I pass a table name as an argument I get the following error
message:

exec sp_help

exec sp_help parcel

Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71
The object 'DGM_HILLSHADE2' does not exist in database 'raster'.

The table clearly exists, but sp_help fails to find it and return
info. This is the case in a couple of my databases. I'm connected as
'sa' so it seems to me it shouldn't be a permissions problem, right?

D Bull

Try selecting the raw information from sysobjects to see if that
offers any clues:

select * from sysobjects where name like '%parcel%' or name like
'%DGM_HILLSHADE2%'

From there, check the "uid" column data (should = 1 if object is owned
by dbo), and run the command:

select object_name(<object_id>)

on the value in the object_id column. This should return the same
name as the "name" column; if it doesn't, then you could have a
corruption in sysobjects.

As Erland points out, you shouldn't be getting an error message about
an object you haven't specified - at worst the SP should simply fail
to find the object if it is owned by someone else.

[db7738]

I apologize. I pieced together my SQL and the result before. I'll try
to handle it consistently here. In database 'plibrary' I'm trying to
get some information about my tables.

use plibrary
go
sp_help

returns a whole list of object, among them:

PARCEL property user table
sp_help parcel

returns:

Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71
The object 'parcel' does not exist in database 'plibrary'.
sp_help property.parcel

returns:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

I don't have this problem with tables in 'Northwind' database, but I do
have the same problem in my 'raster' database.

I have recently restored the 'plibrary' database and had to re-run
sp_defaultdb for a bunch of users because the dbid for database
'plibrary' had changed. I'm wondering if there are other places where
the dbid is stored that may also need to be corrected for the new dbid
maybe?
Okay, onto the next thing.

select * from sysobjects where name = 'PARCEL'

returns (uid = 36):

PARCEL 1688393084 U 36 3 1619460099 384 0 0 2004-01-29
10:59:38.857 0 384 0 U 1 115 0 2004-01-29
10:59:38.857 0 353436333 337436276 353436333 0 0 0
select object_name(36)

returns: NULL

Now I see that if I connect as the table owner 'property' I do get a
result from sp_help, but shouldn't I be able to get that same
information from sp_help if I'm connected as 'sa' or some other user
with read privilege (the table is read-able by public)?

Thanks so much for your help.

D Bull
GIS Programmer/DBA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

<db****@nospam.com> wrote in message
news:40*********************@news.frii.net...

I apologize. I pieced together my SQL and the result before. I'll try
to handle it consistently here. In database 'plibrary' I'm trying to
get some information about my tables.

use plibrary
go
sp_help

returns a whole list of object, among them:

PARCEL property user table
sp_help parcel

returns:

Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71
The object 'parcel' does not exist in database 'plibrary'.
sp_help property.parcel

returns:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

I don't have this problem with tables in 'Northwind' database, but I do
have the same problem in my 'raster' database.

I have recently restored the 'plibrary' database and had to re-run
sp_defaultdb for a bunch of users because the dbid for database
'plibrary' had changed. I'm wondering if there are other places where
the dbid is stored that may also need to be corrected for the new dbid
maybe?
Okay, onto the next thing.

select * from sysobjects where name = 'PARCEL'

returns (uid = 36):

PARCEL 1688393084 U 36 3 1619460099 384 0 0 2004-01-29
10:59:38.857 0 384 0 U 1 115 0 2004-01-29
10:59:38.857 0 353436333 337436276 353436333 0 0 0
select object_name(36)

returns: NULL

Now I see that if I connect as the table owner 'property' I do get a
result from sp_help, but shouldn't I be able to get that same
information from sp_help if I'm connected as 'sa' or some other user
with read privilege (the table is read-able by public)?

Thanks so much for your help.

D Bull
GIS Programmer/DBA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Is your database perhaps case-sensitive? If so, then parcel is not the same
as PARCEL - I notice that your output above is all in upper-case, but when
you use sp_help, you used lower-case.

As for your second point, the uid column in sysobjects is the id of the
owner, not of the object (see Books Online entry for sysobjects) - you
probably meant this:

select object_name(1688393084)

Simon

[Erland Sommarskog]

db****@nospam.com (db****@nospam.com) writes:

sp_help property.parcel

returns:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
When the name consists of several tokens, you need to enclose it in
quotes:

sp_help 'properpy.parcel'
Okay, onto the next thing.

select * from sysobjects where name = 'PARCEL'

returns (uid = 36):

That is definitely not dbo. Thus, to access the table when you are
logged in sa, you must prefix it with the owner. This applies to
SQL statement, as well as sp_help.

The lookup order for a name that is not qualified by user, is to first
see if there is a table with that name owned by the current user, and
if there is not, see if there is a table with that name owned by dbo.

From this follows that dbo must always prefix if he wants to see someone
else's table.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Philip Yale]

db****@nospam.com <db****@nospam.com> wrote in message news:<40*********************@news.frii.net>...

I apologize. I pieced together my SQL and the result before. I'll try
to handle it consistently here. In database 'plibrary' I'm trying to
get some information about my tables.

use plibrary
go
sp_help

returns a whole list of object, among them:

PARCEL property user table
sp_help parcel

returns:

Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71
The object 'parcel' does not exist in database 'plibrary'.
sp_help property.parcel

returns:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

I don't have this problem with tables in 'Northwind' database, but I do
have the same problem in my 'raster' database.

I have recently restored the 'plibrary' database and had to re-run
sp_defaultdb for a bunch of users because the dbid for database
'plibrary' had changed. I'm wondering if there are other places where
the dbid is stored that may also need to be corrected for the new dbid
maybe?
Okay, onto the next thing.

select * from sysobjects where name = 'PARCEL'

returns (uid = 36):

PARCEL 1688393084 U 36 3 1619460099 384 0 0 2004-01-29
10:59:38.857 0 384 0 U 1 115 0 2004-01-29
10:59:38.857 0 353436333 337436276 353436333 0 0 0
select object_name(36)

returns: NULL

Now I see that if I connect as the table owner 'property' I do get a
result from sp_help, but shouldn't I be able to get that same
information from sp_help if I'm connected as 'sa' or some other user
with read privilege (the table is read-able by public)?

Thanks so much for your help.

D Bull
GIS Programmer/DBA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

sp_help doesn't work because you aren't the owner of the object, as
you've acknowledged. The fact that "public" has read access doesn't
change this - that only applies to the data in the table - even if
you're connected as "sa".

The object_name() check should have been given the objectid (you
appear to have passed the uid - did you really mean this?). I don't
think it's important any more as the answer appears to be clear - it's
all down to the table ownership.

sp_help property.parcel won't improve things because
a) it's syntactically incorrect (you need to be in the local
database), and
b) there is no such database as "property" :-)

The fact that you've restored this database is irrelevent; the tables
have no concept of the database ID, since they're entirely
self-contained within it.

The only other issue I can think of which may be affected by dbid's
changing is if you have any VIEWS which include cross-database joins.
Compiled view definitions are stored in syscomments (like stored
procedure code), and reference objects via their dbid's. If a dbid
for an object in a view changes, then the view will fail to work, or
(worse) return data from another database (although that's a little
unlikely). You'd need to drop and recreate the view definition in
this case so that all table names are re-mapped to the correct
dbid/object_id. NB - this is only necessary if the view references
objects in another database, and if it is this other database which
has had a change of dbid.