At some point you might need to denormalize, replacing a view with a materialized view. Now you either need to change every instance in your code, or you need to have a table prefixed with "vw". (Similar troubles apply if you replace a table with a view.)
The entire purpose of views is that from the perspective of the client, it doesn't matter whether it's a table or a view.
If you work with a growing codebase that slowly falls into the (popular) antipattern of DatabaseAsIntegrationPoint, you will end up with dozens of programs spread out over multiple repositories all interacting with some shared tables (not ideal, but it happens).
If you named your tables something like "posts" then I _guarantee_ that if you grepped your entire repository for "posts" that you are going to find an awful lot of false positives in variables and class names. OTOH if you named it "tblposts" then it's far more likely to be a globally unique string identifier.
Why would you need to grep the codebase for a table name you ask?
* prerequisite to a non-additive table ALTER that might have unintended side effects
* prerequisite to trying to undo the carnage of DatabaseAsIntegrationPoint
You shouldn't have to do that in a well-factored appliation, because you're using stored procedures instead of inline SQL.
SELECT COUNT(*) FROM tblposts
Either way gets you some degree of surface area management. Procedures have some added benefit--it's a lot easier to inspect the flow of data when everything is routed through procedure calls. It's a lot easier to put that flow in context when you have a procedure name as a label, provided that your procedures implement a batchful interface.
This approach only makes sense in one of two situations:
1. Ivory Tower DBAs run your company and tell developers "no" at every turn. (sad)
2. Your engineering team makes changing queries hard because they can't hire any developers who know anything about your underlying database platform internals. (also sad)
1. Maintainability. It's necessary to put your SQL on the server if you want to keep it well factored. Just like for any other language, oft-repeated bits of SQL code should be factored out into separate procedures and functions. If you're relying on inline SQL, you're forced to choose between habitually violating the DRY principle or resorting to an unmaintainable mishmash of server-side and client-side queries.
2. Testability. The good unit testing frameworks for SQL code are written in SQL, and designed to be used from an SQL development environment (i.e., the database). And just like for any other language, your SQL code should be covered by good tests.
There are plenty of tools out there to help with deployment if it's causing difficulties for you. I recommend using them if that's what it takes for you to be comfortable with the platform.
So most recently, we had an app that started off with direct table access via an ORM. Once the data access paths stabilized somewhat, I started replacing them with stored procedures. Those stored procedures gradually coalesced to form an API. The ORM-like functionality is still there, if need be, but the stored procedures now provide a contract, much like a service.
In retrospect, I'm not sure the ORM was even that useful. Besides encouraging certain bad habits on the consumer side (eg, most instances of lazy loading), its one more level of indirection to grapple with. Why not drop down to the database and write your implementation there? It can be tested right there and then, and directly in terms of the data flow: input -> output.
I agree, the client shouldn't care about the data being a table or a view. But as a DBA, if I need to quickly find a solution to a problem, the use of a prefix can be a benefit.
There are costs and risks for all design choices. If you are working with a system that is growing and prone to the need of denormalizing frequently then perhaps using a prefix isn't the right choice.
But not every system has that issue. I see more cases these days of smaller databases...think "one database per customer" type of architecture. Denormalization isn't an issue often, and prefixes seem to work just fine.