March 14, 2004 at 11:07 pm
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
Distributed transactions use four part names.
Don't know if this will work, but give it a try.
SELECT * FROM DeptSQLSrvr.Northwind.dbo.EmployeesSelect *From servername.databasename.ownername.tablenameI believe ommision of servername will default to local server.GaryAMarch 15, 2004 at 2:37 pm
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?
March 15, 2004 at 2:42 pm
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
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
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
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 )
March 23, 2004 at 10:23 pm
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
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> |
March 24, 2004 at 5:28 am
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