473,882 Members | 1,591 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

sp_help can't see my tables

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
Jul 20 '05 #1
6 18886
D Bull (db****@yahoo.c om) 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.pa rcel'

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
Jul 20 '05 #2
db****@yahoo.co m (D Bull) wrote in message news:<26******* *************** ****@posting.go ogle.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_HILLSHADE 2%'

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

select object_name(<ob ject_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.
Jul 20 '05 #3
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!
Jul 20 '05 #4

<db****@nospam. com> wrote in message
news:40******** *************@n ews.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(168 8393084)

Simon
Jul 20 '05 #5
db****@nospam.c om (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.parce l'
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
Jul 20 '05 #6
db****@nospam.c om <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.
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4188
by: Dave C. | last post by:
Hi, I have a few things on my databases which seem to be neither true system objects or user objects - notably a table called 'dtproperties' (created by Enterprise manager as I understand, relating to relationship graphing or something) and some stored procs begining with "dt_" (some kind of source control stuff, possible visual studio related). These show up when I use "exec sp_help 'databaseName'"
1
480
by: Dax Westerman | last post by:
Can anyone tell me if there's a way to create new windows from the tables section of a database in Enterprise Manager such that I can limit the tables included in that window? Ultimately, I'm looking for a solution whereby I can group tables, stored procedures, etc. into logical groups that can then be displayed using different MMC consoles. I'm looking for either a GUI-level or programatic approach. Thank you, Dax Westerman
44
3913
by: Mariusz Jedrzejewski | last post by:
Hi, I'll be very grateful if somebody can explain me why my Opera 7.23 (runing under linux) doesn't show me inner tables. Using below code I can see only "inner table 1". There is no problem with other browsers (I checked it under Konqueror). Thank you in advance for your help. Regards. /Mariusz <HTML>
3
1749
by: Arno R | last post by:
Hi all, Starting from Access 2k we can use tables and queries directly in the Subform control as a SourceObject. The Subform-Sourceobject can be assigned when a query or table has been chosen from a combobox. e.g. Me.Subform.SourceObject = "Table." & Me.cboList Me.Subform.SourceObject = "Query." & Me.cboList How to do the same with attached tables? Any workaround other than create a query based on the attached table?
15
2175
by: Gary Peek | last post by:
Can anyone tell us the browsers/versions that exhibit errors when tables are nested too deeply? And how many levels of nesting produces errors? (not a tables vs CSS question)
2
1412
blossam
by: blossam | last post by:
Hi frnd, I have created an application in vb.net for which i have create database in mysql 4.1 I want to secure all tables of mysql database in such way that no one can see structure or data of any table. how can i set password to mysql database? and even if i set password to database someone can copy tables in other database and can aaccess the tables, give me some idea for securing mysql tables
2
1128
by: Abdul salam A | last post by:
I hv table. My task is no body shouldt see the table stru. I.e if a person do the following code and executed. use model sp_help employee ---------------------------------------- #$^$&^%^%&(^((^*^78 The db shoud not retun anything but some of pnumonic code. so, hw can i hide it. i.e how can i encrypt. pls giv me the idiea. (even i know we couldt decrypt). This is for temp. purpose only
1
1079
by: Leon Sumter | last post by:
Can an Access 2007 table consisting mainly of lookup columns be sorted according to the fields in the lookup columns. My other subordinate non-lookup tables are all sortable. Leon
3
2299
by: tetsuo2030 | last post by:
Greetings all, (Access 2003) The bosses want me to take an old version of a database designed with a single-user-at-a-time mentality and update it to allow multiple, simultaneous users. The biggest trouble is that, based on its current design, simultaneous users could be potentially overwriting each other's data. I do have a work-around: I'll set up new temp-tables by capturing their in-house network usernames (JOHNSMITH) and treat it...
0
9931
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9777
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11108
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10403
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7956
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4601
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4198
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3226
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.