Find views using Specific Tables (Information)

Leveraging Views in SQL for Optimal Data Presentation

Views in SQL serve as virtual tables, presenting data from one or more tables through a predefined query. They offer a plethora of advantages, from security to abstraction to simplifying complex queries. This guide provides an authoritative walkthrough on how to utilize views to present database table information effectively.

Definition of a View: A view is a saved SQL query or a virtual table, which draws data from one or more tables. It doesn't store data itself, but rather displays it based on the underlying query.
```
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
```
Advantages of Using Views:
- Abstraction: Mask the complexity of underlying data structures, presenting only relevant columns.
- Security: Limit access to specific columns or rows, ensuring sensitive data remains concealed.
- Consistency: Provide a uniform interface to data, regardless of changes to underlying tables.
Creating a View:
To establish a view, use the `CREATE VIEW` statement.
```
CREATE VIEW EmployeeOverview AS
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
WHERE Active = 1;
```
In this example, `EmployeeOverview` presents active employees and omits other details like salaries or contact information.

Querying a View: Treat a view like any other table when querying:

SELECT FirstName, LastName
FROM EmployeeOverview
WHERE Department = 'IT';

Updating a View: If the structure or conditions of a view need modification, utilize the `CREATE OR REPLACE VIEW` statement.
```
CREATE OR REPLACE VIEW EmployeeOverview AS
SELECT EmployeeID, FirstName, LastName, Department, HireDate
FROM Employees
WHERE Active = 1;
```
Here, `HireDate` has been added to the view.
Inserting, Updating, and Deleting Using a View: While views are primarily for data retrieval, they can facilitate data modification. However, the ability to insert, update, or delete data via a view depends on its structure and the underlying tables. For instance, updating data:
```
UPDATE EmployeeOverview
SET Department = 'HR'
WHERE LastName = 'Smith';
```
Dropping a View: To remove a view, utilize the `DROP VIEW` statement.
```
DROP VIEW EmployeeOverview;
```
Considerations and Limitations:
- Performance: Since views don't store data, each query against a view fetches data from the underlying tables, which might affect performance.
- Complexity: Views drawing from multiple tables or involving complex operations can sometimes be slower.
- Dependencies: Alterations to underlying tables (like dropping a column) might invalidate the view.

Views, as an integral part of SQL, provide a powerful mechanism to present data from database tables efficiently. By abstracting complexities and bolstering data security, they facilitate a streamlined and secure data access strategy. When harnessed judiciously, views can be instrumental in optimizing database interactions, reinforcing the power and flexibility of SQL in data management tasks.

I have to add a few columns to a table and I also need to add these columns to all the views that use this table.
Question: Is it possible to get a list of all the views in a database that use a certain table?
Solution 1:

SELECT * 
FROM   INFORMATION_SCHEMA.VIEWS 
WHERE  VIEW_DEFINITION like '%[YourTableName]%'

Solution 2:

SELECT * FROM INFORMATION_SCHEMA.VIEWS 
WHERE VIEW_DEFINITION 
like '%YourTableName.%' OR 
VIEW_DEFINITION like '%YourTableName]%' OR 
VIEW_DEFINITION like '%YourTableName %'

In the next module, you will learn about using views to show information in your tables in a slightly different manner. Views are important tools that can make using the information in your databases more straightforward, and can save you time and effort if you provide access to your database tables to other users. When you CREATE OR REPLACE a PL/SQL program, the source code for that program along with other representations of that software is stored in the database itself and exposed through a wide range of data dictionary views. This is a tremendous advantage for two key reasons:

Information about that code is available to you via the SQL language.
I can write queries and even entire PL/SQL programs to read the contents of these data dictionary views and obtain lots of fascinating and useful information about my code base.
The database manages dependencies between your stored objects
For example, if a stored function relies on a certain table, and that table's structure is changed, the status of that function is automatically set to INVALID.
Recompilation then takes place automatically when someone tries to execute that function.

This SQL interface to your code base allows you to manage your code repository running analyses on your code, documenting what has been written and changed, and so on.
The following sections introduce you to some of the most commonly accessed sources of information in the data dictionary.