In this article we are going to see, how to get the definition of system views, we are using the following query to get the tables present inside the database, but how to find the definition behind that views, Here is the answer.
Query :
Execute the following query in SSMS to get the Definition for SYS.TABLES
Query :
SELECT * FROM SYS.TABLES
SELECT OBJECT_DEFINITION(OBJECT_ID('SYS.TABLES'))
OBJECT_DEFINITION will give the definition of that object.
Result :
CREATE VIEW SYS.TABLES AS
SELECT O.NAME
,O.OBJECT_ID
,O.PRINCIPAL_ID
,O.SCHEMA_ID
,O.PARENT_OBJECT_ID
,O.TYPE
,O.TYPE_DESC
,O.CREATE_DATE
,O.MODIFY_DATE
,O.IS_MS_SHIPPED
,O.IS_PUBLISHED
,O.IS_SCHEMA_PUBLISHED
,LOB.LOBDS AS LOB_DATA_SPACE_ID
,RFS.INDEPID AS FILESTREAM_DATA_SPACE_ID
,O.PROPERTY AS MAX_COLUMN_ID_USED
,O.LOCK_ON_BULK_LOAD
,O.USES_ANSI_NULLS
,O.IS_REPLICATED
,O.HAS_REPLICATION_FILTER
,O.IS_MERGE_PUBLISHED
,O.IS_SYNC_TRAN_SUBSCRIBED
,O.HAS_UNCHECKED_ASSEMBLY_DATA
,LOB.INTPROP AS TEXT_IN_ROW_LIMIT
,O.LARGE_VALUE_TYPES_OUT_OF_ROW
,O.IS_TRACKED_BY_CDC
,O.LOCK_ESCALATION_OPTION AS
LOCK_ESCALATION
,TS.NAME AS
LOCK_ESCALATION_DESC
FROM SYS.OBJECTS$ O
LEFT
JOIN SYS.SYSIDXSTATS
LOB
ON LOB.ID = O.OBJECT_ID
AND LOB.INDID <= 1
LEFT
JOIN SYS.SYSSINGLEOBJREFS
RFS
ON RFS.DEPID = O.OBJECT_ID
AND RFS.CLASS = 42
AND RFS.DEPSUBID = 0
/*
SRC_OBJTOFSDS LEFT JOIN
SYS.SYSPALVALUES TS ON TS.CLASS = 'LEOP'
AND
TS.VALUE = O.LOCK_ESCALATION_OPTION
WHERE O.TYPE = 'U' */
I Hope this article will help you to find the definition for system defined views.
No comments:
Post a Comment