April 25, 2022 at 4:47 am
Hi all,
I uploaded Adventurework2019.bak file to do a course on Udemy and found that all the tables have a prefix.tablename nomenclature. Is there a way to remove all the schema prefixes ?
When I try DROP SCHEMA IF EXISTS PRODUCTION, I get a message schema cannot be dropped as it is being referenced by another object
thanks
April 25, 2022 at 9:42 am
All tables in SQL Server belong to a schema. What problem are they causing you?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 25, 2022 at 9:50 am
No major problems as such. Mainly just ease of writing queries, that's all. At work, I have noticed that noen of the tables have any query name specified as the prefix. And since I have query shortcuts assigned for simple tasks like countrows and count top 1000, it makes it easier since I double-click on the query name to highlight it
Whereas with the prefix, I have to select the entire query name which is something like AdventureWorks2019.Product.ProductDetails instead of just ProductDetails
I was just curious as to why schema names are used. One reason I can think of is to discriminate between tables having the same names but can't think of anything else
Regards
April 25, 2022 at 10:22 am
Product.ProductDetails is what you should be including in your query. Including database name is usually a bad idea (if you ever rename the database, the query will no longer return the expected results).
Putting table names without schema names is bad practice and may generate unnecessary cache-miss events (link). You should consider refactoring the queries at your work to include it, and educate your colleagues in the process.
It is common practice to use schema names as a way of enforcing granular levels of security within a database. Imagine a database which contains a mixture of internal (not for users) tables and reporting tables. If you create two schemas (int and rep, or whatever) and assign the tables to whichever schema is relevant, you can then GRANT SELECT access to all objects in rep, but leave objects in schema int inaccessible.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 25, 2022 at 10:25 am
That's interesting. A lot you just said which I didn't know earlier! Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply