Post reply

Namespace Concept in SQL-SERVER

  • March 14, 2004 at 11:07 pm

    #86259

    I have some transactional tables,some Masters and customization tables,but to identify these i dont have any criteria,due to client specification i am unable to put suffix these tables to identify in my db ,so there is any other way (just like namespace in oracle) to identify the tables with out changing the name.

    Thanks

  • March 15, 2004 at 1:01 pm

    #498816

    Distributed transactions use four part names.

    Don't know if this will work, but give it a try.

    SELECT * 
    FROM DeptSQLSrvr.Northwind.dbo.Employees
    Select *
    From servername.databasename.ownername.tablename
    I believe ommision of servername will default to local server.
    GaryA
     

  • March 15, 2004 at 2:37 pm

    #498841

    I'm not quite sure what you mean. There is a schema, but that likely would not work since you mention app customizations.

    Are you trying to provide information to end users? Since your development community is likely small, would a good up to date data dictionary suffice?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • March 15, 2004 at 2:42 pm

    #498844

    When you mention "namespaces" in Oracle, do you mean tablespaces?  If so, you can put objects into specific filegroups in SQL Server and use that as a means of identifying objects.  That's not what they're normally used for, but ... well, that's not what tablespaces are usually used for either.

    If you really mean "namespace" then you must be talking XML namespace, and you'll have to post more specifically.

    If you mean "schema" then Steve Jones has already addressed that idea.

    Cheers,

    Chris

  • March 15, 2004 at 9:26 pm

    #498890

    This is not schema and db is same but you can say table spaces,bcz i only need the identifications of tables,which is transactional,customizationa etc .plz suggest

  • March 22, 2004 at 11:34 pm

    #499945

    Umm, not really sure what you mean, but if you are trying to identify which tables are what, ie, TableA is for transactional data and TableB is for customizations why don't you name your tables:

    TRN_TableA

    CUST_TableB

    Is this what you're trying to do?

    Angela

  • March 23, 2004 at 12:05 am

    #499948

    If this is going to be used only as a Identification purpose ...

    you can either create a small table containing TableName and TableType

    or

    Use Extended Properties to DEFINE Table Types for each table.

    (refer to BOL for extended properties )

     

     


    -- Amit


    "There is no 'patch' for stupidity."


    Download the Updated SQL Server 2005 Books Online.

  • March 23, 2004 at 10:23 pm

    #500150

    Thanks Amit

    but what i understand this extended property will be on column base ,Can i apply this for table level also?

  • March 23, 2004 at 10:28 pm

    #500151

    Yes, it can be at table level. As per BOL (Topic: Using Extended Properties on Database Objects).

     

    For the purposes of specifying extended properties, the objects in a SQL Server 2000 database are classified into three levels (0, 1, 2). Level 0 is the highest level and 2 is the lowest level. The table lists the level-0 objects, user and user-defined data type, with their valid level-1 and level-2 objects.

    Level 0 Level 1 Level 2
    User Table Column, index, constraint, trigger
    View Column, INSTEAD OF trigger
    Schema-bound view Column, index, INSTEAD OF trigger
    Stored procedure Parameter
    Rule <None>
    Default <None>
    Function Column, parameter, constraint,
    Schema-bound function Column, parameter, constraint
    User-defined data type <None> <None>


    -- Amit


    "There is no 'patch' for stupidity."


    Download the Updated SQL Server 2005 Books Online.

  • March 24, 2004 at 5:28 am

    #500182

    I am not 100% sure but I believe you are referring to schema in Oracle not namespace. And use tables can have dirrent schema owners (object owner) so you can use the same name for tables. My understanding of namespace with oracle is that certain objecdts cannot have the same name such as a table named B exists under schema ADMIN so you cannot name a view B under ADMIN, however schema USER1 can have a table B also because the schema controls a protion of the namespace requirements.

    Although not suggested you can change the shema owner of an object and have multiple tables with the same name. The key is to always remember you must use at least the 2 part name schema.object or User1.Table1 for instance. When you create an object you do like so:

    CREATE [OBJECTTYPE] schema.objectname

    To alter an existing one you can use

    sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

    (See more details BOL)

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

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