A New View

Congratulations to Vadim Rapp, owner of Vadim Rapp Consulting in Park Ridge, Illinois, and Chris Hedgate, a senior DBA for Extralives in Malmo, Sweden. Vadim won first prize of $100 for the best solution to the October Reader Challenge, "A New View." Chris won second prize of $50. Here’s a recap of the problem and the solution to the October Reader Challenge.

Problem:

Greg is a database analyst for a company hosting databases on SQL Server 2000 and 7.0 servers. He has several users creating objects for applications in their respective schema. The users don't have permissions to query system tables directly and have been explicitly denied access to system tables such as sysobject and syscomments. The users obtain most schema information from system stored procedures, information schema views, and metadata functions. Greg wants to provide users with a view that gives them details about computed columns on tables because this information isn't available from any system stored procedures or views. For simplicity, he wants to create a view modeled after the existing information schema view.

Help Greg create a view named INF_SCHEMA_COMPUTED_COLUMNS that satisfies the following requirements:

1. Any user who has access to the database should be able to query the view.
2. The view should list only user-defined tables that contain computed columns.
3. The view should list only tables or columns for which the user has permissions to select.
4. The view should provide the owner, name of the table, column name, ordinal position, and expression for the computed columns.

Solution:

Greg can obtain the columns for each table from the syscolumns system table. The expression for the computed columns is stored in syscomments. For each computed column in a table, syscomments contains a row with a number column that stores the column’s ordinal position. The following query returns the computed columns for each table, given an ordinal position:

SELECT c1.id, c1.name, c1.colid, c2.text
  FROM syscolumns AS c1
  JOIN syscomments AS c2
    ON c1.id = c2.id
   AND c2.number = c1.colid

Greg can use the OBJECTPROPERTY() metadata function to filter tables that aren’t user-defined and the COLUMNPROPERTY() function to retrieve only computed columns. For any user-defined object, the IsMSShipped property returns 0. This value can help Greg find the user-defined tables. The check for COLUMNPROPERTY isn’t mandatory because the query has a join condition between syscolumns and syscomments. The following query contains these additional conditions:

SELECT c1.id, c1.name, c1.colid, c2.text
  FROM syscolumns AS c1
  JOIN syscomments AS c2
    ON c1.id = c2.id
   AND c2.number = c1.colid
 WHERE COLUMNPROPERTY( c1.id, c1.NAME, 'IsComputed' ) = 1
   AND OBJECTPROPERTY( c1.id, 'IsMSShipped' ) = 0

Greg uses the built-in PERMISSIONS() function to filter the tables that the user doesn’t have permissions to select from. The PERMISSIONS() function tests whether the user has permission to select all the columns in the table or if only granted access to the computed column. The following query performs this additional check:

SELECT c1.id, c1.name, c1.colid, c2.text
  FROM syscolumns AS c1
  JOIN syscomments AS c2
    ON c1.id = c2.id
   AND c2.number = c1.colid
 WHERE COLUMNPROPERTY( c1.id, c1.NAME, 'IsComputed' ) = 1
   AND OBJECTPROPERTY( c1.id, 'IsMSShipped' ) = 0
   AND PERMISSIONS( c1.id, c1.name )&1 = 1

Finally, to obtain the object name and owner ID information, Greg uses the OBJECT_NAME() and OBJECTPROPERTY() functions. In the database, the owner ID can be translated to user name by using the USER_NAME() system function. The following query returns the object name and owner ID information:

SELECT USER_NAME( OBJECTPROPERTY( c1.id, 'OwnerID' ) ), OBJECT_NAME( c1.id ), c1.name, c1.colid, c2.text

Here is the complete INF_SCHEMA_COMPUTED_COLUMNS view definition:

CREATE VIEW INF_SCHEMA_COMPUTED_COLUMNS
( TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_EXPR )
AS
SELECT USER_NAME( OBJECTPROPERTY( c1.id, 'OwnerID' ) ), OBJECT_NAME( c1.id ), c1.name, c1.colid, c2.text
  FROM syscolumns AS c1
  JOIN syscomments AS c2
    ON c1.id = c2.id
   AND c2.number = c1.colid
 WHERE COLUMNPROPERTY( c1.id, c1.NAME, 'IsComputed' ) = 1
   AND OBJECTPROPERTY( c1.id, 'IsMSShipped' ) = 0
   AND PERMISSIONS( c1.id, c1.name )&1 = 1
GO
GRANT SELECT ON INF_SCHEMA_COMPUTED_COLUMNS TO PUBLIC
GO

With any dbo account on the sample Northwind database you can use the code at the end of this paragraph to test the view’s functionality. The code creates a copy (CalculatedSales) of the OrderDetails table and adds three calculated columns: LinePrice, DiscountedAmount, and LinePriceAfterDiscount. Then it grants the guest user account SELECT permission on the OrderID, ProductID, and LinePriceAfterDiscount columns only. The code then uses the SETUSER command to impersonate the guest account to read the data from CalculatedSales table and verify the INF_SCHEMA_COMPUTED_COLUMNS results.

USE Northwind
GO

-- Create a quick copy of the Order Details table.
SELECT * INTO CalculatedSales
 FROM \[Order Details\]
GO

-- Add three calculated columns.
ALTER TABLE CalculatedSales
  ADD TotalLinePrice AS UnitPrice * Quantity,
  	    DiscountedAmount AS (UnitPrice*Quantity)*Discount,
  	    LinePriceAfterDiscount AS (UnitPrice*Quantity)- (UnitPrice*Quantity)*Discount 
GO

-- Look at the results.
SELECT * FROM CalculatedSales

-- Grant SELECT permissions on a few columns to the "guest" user account.
GRANT SELECT ON CalculatedSales(OrderID, ProductID, LinePriceAfterDiscount)
  TO guest

-- Impersonate the guest user account.
SETUSER 'guest'

-- Now retrieve those columns that "guest" can see.
SELECT OrderID, ProductID, LinePriceAfterDiscount FROM CalculatedSales

-- Test the view to make sure only the LinePriceAfterDiscount computed column is displayed.
SELECT * FROM inf_schema_computed_columns
 WHERE TABLE_NAME = 'Calculatedsales'

-- Reset user context switch.
SETUSER

NOVEMBER READER CHALLENGE:

Now, test your SQL Server savvy in the November Reader Challenge, "To Manipulate Tables" (below). Submit your solution in an email message to [email protected] by October 16. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Peter is the database architect for a company that builds accounting applications using SQL Server 7.0 and 2000. The applications have two databases, APP and ACCT, that reside on different SQL Servers or instances. The application code connects only to the APP database; Peter accesses the ACCT database primarily through distributed queries from stored procedures. Peter knows the ACCT database server’s name only at setup or installation time, so when he initially implemented the code, he used dynamic T-SQL and distributed queries as the following example shows:

-- Get name of ACCT database server from configuration table:
SET @AcctSrvr = '\[Name_Of_Srvr_containing_ACCT_DB\]'
EXEC('SELECT * FROM OPENQUERY(' + @AcctSrvr + ', "SELECT * FROM ACCT..users'')')

During setup, Peter uses the system stored procedure sp_addlinkedserver to create and configure the linked server for the ACCT database based on user input. Peter finds that approach tedious and hard to maintain, especially for complex queries. He wants to create views on remote tables that he can easily manipulate. Help Peter:

1. Eliminate the dependency on a linked server, keeping the same two-server approach.
2. Eliminate the need for dynamic SQL.
3. Use static T-SQL code to write distributed queries in stored procedures.