Using Views to Simplify Retrieval of Data

When querying data, it is often required that complex joins, aggregates, functions, and so on, be written in to the SELECT statement to produce the desired results. To mask this complexity, the SELECT statement can be created as a view, and then future queries can be performed against the view. The information_schema views provided by Microsoft are a prime example of this use of a view. Because the system tables used to store metadata are quite complex, developing queries to retrieve information about SQL Server can be a daunting task. By querying the information_schema views instead of the system tables, you can simplify your SELECT statements. Following is a simple SELECT statement to query the information_schema.table_privileges view:

 SELECT * from Information_schema.table_privileges 

This query will obtain information about permissions in the database.

In Listing 27.1, you see the actual SELECT statement with which the table_privileges view is defined. Both statements return the same data, so it's up to you to decide which you would rather type!

Listing 27.1 SELECT Statement Defining the information_schema.table_privileges View

 select         user_name(p.grantor)  as GRANTOR        ,user_name(p.uid)             as GRANTEE        ,db_name()                            as TABLE_CATALOG        ,user_name(o.uid)             as TABLE_SCHEMA        ,o.name                                     as TABLE_NAME        ,case p.action                when 26  then 'REFERENCES'                when 193 then 'SELECT'                when 195 then 'INSERT'                when 196 then 'DELETE'                when 197 then 'UPDATE'         end                                         as PRIVILEGE_TYPE        ,case                when p.protecttype = 205 then 'NO'                else 'YES'         end                                        as IS_GRANTABLE  from         sysprotects p,         sysobjects o where         (is_member(user_name(p.uid)) = 1         or                p.grantor = user_id())    and (p.protecttype = 204 or  /*grant exists without same grant with grant */    (p.protecttype = 205                and not exists(select * from sysprotects p2                               where p2.id = p.id and                               p2.uid = p.uid and                               p2.action = p.action and                               p2.columns = p.columns and                               p2.grantor = p.grantor and                               p2.protecttype = 204)))        and p.action in (26,193,195,196,197)        and p.id = o.id        and o.xtype in ('U', 'V')        and 0 != (permissions(o.id) &                case p.action           when 26  then  4               /*REFERENCES basebit on all columns */           when 193 then  1               /*SELECT basebit on all columns     */           when 195 then  8               /*INSERT basebit */           when 196 then  16              /*DELETE basebit */           when 197 then  2               /*UPDATE basebit on all columns     */                end)