Meta-data

for RuBoard

The OBJECTPROPERTY() meta-data function supports a number of properties that relate to views. Table 9-1 summarizes them.

Table 9-1. View-Related OBJECTPROPERTY() Values

Property	Function
ExecIsAnsiNullsOn	Indicates whether ANSI_NULLS was on when the view was created or altered
ExecIsQuotedIdentOn	Indicates whether QUOTED_IDENTIFIER was on when the view was created or altered
HasAfterTrigger	Indicates whether the view has an AFTER trigger
HasInsertTrigger	Indicates whether the view has an INSERT trigger
HasInsteadOfTrigger	Indicates whether the view has an INSTEAD OF trigger
HasUpdateTrigger	Indicates whether the view has an UPDATE trigger
IsAnsiNullsOn	Indicates whether ANSI_NULLS was on when the view was created or altered (same as ExecIsAnsiNullsOn)
IsDeterministic	Indicates whether the view consistently returns the same results given the same criteria
IsExecuted	Returns 1 for views and other executable objects: triggers, stored procedures, UDFs, and tables with computed columns
IsIndexable	Indicates that an index can be created over the view
IsIndexed	Indicates that the view is indexed
IsQuotedIdentOn	Indicates whether QUOTED_IDENTIFIER was on when the view was created or altered (same as ExecIsQuotedIdentOn)
IsSchemaBound	Indicates whether the view is schema bound
IsView	Returns 1 for views

Listing the Source Code for a View

Unless a view was created using the WITH ENCRYPTION option, you can use sp_helptext to retrieve its source code. You can also inspect and modify view source code in Enterprise Manager, as well as many SQL-DMO-enabled administration tools. Here's some sample code that returns the source of the syslogins system view (Listing 9-1):

Listing 9-1 sp_helptext can list a view's source code.

 USE master EXEC sp_helptext syslogins Text --------------------------------------------------------------------------- CREATE VIEW syslogins AS SELECT     sid = convert(varbinary(85), sid),     status = convert(smallint, 8 +             CASE WHEN (xstatus & 2)=0 THEN 1 ELSE 2 END),     createdate = convert(datetime, xdate1),     updatedate = convert(datetime, xdate2),     accdate = convert(datetime, xdate1),     totcpu = convert(int, 0),     totio = convert(int, 0),     spacelimit = convert(int, 0),     timelimit = convert(int, 0),     resultlimit = convert(int, 0),     name = convert(sysname, name),     dbname = convert(sysname, db_name(dbid)),     password = convert(sysname, password),     language = convert(sysname, language),     denylogin = convert(int, CASE WHEN (xstatus&1)=1 THEN 1 ELSE 0 END),     hasaccess = convert(int, CASE WHEN (xstatus&2)=2 THEN 1 ELSE 0 END),     isntname = convert(int, CASE WHEN (xstatus&4)=4 THEN 1 ELSE 0 END),     isntgroup = convert(int, CASE WHEN (xstatus&12)=4 THEN 1 ELSE 0 END),     isntuser = convert(int, CASE WHEN (xstatus&12)=12 THEN 1 ELSE 0 END),     sysadmin = convert(int, CASE WHEN (xstatus&16)=16 THEN 1 ELSE 0 END),     securityadmin = convert(int, CASE WHEN (xstatus&32)=32 THEN 1 ELSE 0 END),     serveradmin = convert(int, CASE WHEN (xstatus&64)=64 THEN 1 ELSE 0 END),     setupadmin = convert(int, CASE WHEN (xstatus&128)=128 THEN 1 ELSE 0 END),     processadmin = convert(int, CASE WHEN (xstatus&256)=256 THEN 1 ELSE 0 END),     diskadmin = convert(int, CASE WHEN (xstatus&512)=512 THEN 1 ELSE 0 END),     dbcreator = convert(int, CASE WHEN (xstatus&1024)=1024 THEN 1 ELSE 0 END),     bulkadmin = convert(int, CASE WHEN (xstatus&4096)=4096 THEN 1 ELSE 0 END),     loginname = convert(sysname, name) FROM sysxlogins WHERE srvid IS NULL 

for RuBoard