لطفا منتظر باشید ...
Meta-data
The OBJECTPROPERTY() meta-data function supports a number of properties that relate to views. Table 9-1 summarizes them.
| 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
