Post reply

Need Clarification on Nomenclature for sys and dbo

  • December 4, 2009 at 3:50 pm

    #218315

    Hi folks

    kind of a stupid question to ask, but looking at several pieces of code i am confused on the differences when i see dbo and sys

    prefacing an object

    an example would be like

    master.sys.sysprocesses vs master.dbo.sysprocesses

    what is the difference and when to use each or is there any ?

    when i do an sp_help, the info looks the same to me

    Thanks

    Jim

  • December 4, 2009 at 4:42 pm

    #1088082

    When you create a procedure using sp_xxxx

    SQL Server interprets the sp_ such that it looks for the procedure in the master database. In this manner for example if I create a procedure in one of my databases and name is sp_xxx SQL server will NOT run my procedure, but rather the sp_xxx procedure if finds in the master database.

    Or better iin your local database run dbo.sp_help_revlogin (of course after checking that it is NOT in your local DB... it runs.

    or as an alternative

    To prove this create a procedure say sp_Help in your local database to simply print a statement. Then while in your local database attempt to run it. It will run the sp_help that is in the Master DB

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • December 4, 2009 at 5:32 pm

    #1088086

    Beyond what bitbucket wrote, the difference in those calls is dbo and sys are schemas. Schemas are buckets for organizing objects and applying security.

    The dbo and sys are system schemas. Many people that don't want to mess with schemas will create all their objects in their own databases in the dbo schema. However if you want to create your own schema, like "Sales" for all sales related tables, you can do that.

  • December 7, 2009 at 9:21 am

    #1088650

    Hi Folks

    so if i wanted to pull info from either of these two tables which schema should i be using then; dbo or sys ?

    is there always a 1 to 1 correlation for these sys type tables in the dbo schema and the sys schema ?

    Thanks

    Jim

  • December 7, 2009 at 9:42 am

    #1088680

    JC-3113 (12/7/2009)

    so if i wanted to pull info from either of these two tables which schema should i be using then; dbo or sys ?

    For sysprocesses either works, because it is a backward compatibility view, it's there just so that code from SQL 2000 will work, it should not be used any longer, is deprecated and will be removed in a future version of SQL Server

    is there always a 1 to 1 correlation for these sys type tables in the dbo schema and the sys schema ?

    No.

    SELECT * FROM sys.objects

    -- 75 rows returned

    SELECT * FROM dbo.objects

    -- Msg 208, Level 16, State 1, Line 1

    -- Invalid object name 'dbo.objects'.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 7, 2009 at 10:20 am

    #1088710

    Thanks Gail

    are you saying sysprocesses should be used then ?

    if so, what is replacement ?

    jim

  • December 7, 2009 at 10:20 am

    #1088711

    whoops..

    I meant "not be" used"

    Jim

  • December 7, 2009 at 10:26 am

    #1088716

    A quick look in Books Online under the topic 'sysprocesses' (http://msdn.microsoft.com/en-us/library/ms179881%28SQL.90%29.aspx) shows the following

    Books Online

    sys.sysprocesses

    This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 7, 2009 at 11:15 am

    #1088748

    Thanks Gail

    will take a look see

    Jm

Viewing 9 posts - 1 through 8 (of 8 total)

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