Table Naming Convention Questions

Table Naming Convention Questions

Post by don garr » Wed, 06 Jun 2001 14:30:28



I'm new to ADPs and SQL and am trying to understand the significance and
the security ramifications of using Tables which use the dbo.TableName
convention vs just using the TableName.

I've also noticed that when I'm creating Views that it automatically
puts the dbo. designation in the SQL statements, then when I transfer
these tables using DTS from my local Server to our Corporate Server it
takes on my Username.TableName designation which causes my stored
procedures not to run correctly (I'm not the dbo on the corporate server
but I have full administration rights).

Does this mean that I'll have a problem in a multi user environment or
will giving the user execution rights to the stored procedure eliminate
the issue ?

I sure appreciate it if someone could fill me in at to what is happening
on this issue or which convention would be best.

 
 
 

Table Naming Convention Questions

Post by Mary Chipma » Wed, 06 Jun 2001 23:31:24


In an ADP, make sure that anything you code uses the
ownername.objectname syntax (see SQL BOL for more information). SQL
Server allows duplicate object names as long as they have different
owners, so the owner name is an important part of the syntax. If dbo
(a system administrator) creates an object, you see "dbo.objectname".
If user Fred logs on and does a "SELECT * from objectname", then SQL
Server looks for an object named "fred.objectname", not
dbo.objectname. HTH,

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446



Quote:>I'm new to ADPs and SQL and am trying to understand the significance and
>the security ramifications of using Tables which use the dbo.TableName
>convention vs just using the TableName.

>I've also noticed that when I'm creating Views that it automatically
>puts the dbo. designation in the SQL statements, then when I transfer
>these tables using DTS from my local Server to our Corporate Server it
>takes on my Username.TableName designation which causes my stored
>procedures not to run correctly (I'm not the dbo on the corporate server
>but I have full administration rights).

>Does this mean that I'll have a problem in a multi user environment or
>will giving the user execution rights to the stored procedure eliminate
>the issue ?

>I sure appreciate it if someone could fill me in at to what is happening
>on this issue or which convention would be best.


 
 
 

1. system tables - link SP name and Table Name?

If you look at this...

select *
from sysobjects so
inner join syscolumns sc ON so.id = sc.id
inner join systypes st on sc.xtype = st.xtype
where sc.id = object_id('YOUR-STORED-PROC') and sc.isoutparam<>1 order by
colorder

You see lots of info about your stored procedure except for the table
name(s) it calls.  That's the problem.  I want to somehow link that info to
the table name so I can figure out if a insert stored procedure can pass
NULLS to certain table fields.  But I need to know the answer to the
'isnullable' field for the TABLE (not the stored proc).  See this example:

select * FROM sysobjects
         INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
         where sysobjects.name = 'YOUR-TABLE' ORDER BY syscolumns.colid

If I can somehow combine them, then I can make a code generator that runs
against "Insert" stored procedures and the output is text for the start of a
VB function.  I can do this now, except I can't display "Optional" before my
VB function arguments because I don't know whether the stored proc can pass
along NULLS until I can somehow link from the SP name to the TABLE name and
get that 'isnullable' table value.  My desired final code generator would
be:

exec MyCodeGenerator 'mySPname'
  >>
"Public Function (firstname as string, Optional lastname as string) as
string"

But I can't conditionally write "Optional" until I can figure out a link
between the stored procedure and the table name in the system tables.

2. ADODB.Command Error.

3. Accessing another user's table without qualifying table name with the owner-name

4. Stored Procedure that breaks periodically

5. joined tables same field names (NEWBIE QUESTION)

6. Datawarehouse with Oracle

7. Table and field names question

8. how to pass extra parameter to sybase err_handler

9. DBF long table names - newbie question

10. HEY GURUS - Question About Table Names In SQLServer

11. OCI question - getting table name from OCIRef *

12. variable table name SP /permission question

13. SQL 7.0 Question..changing table names