Tuesday 5 November 2013

Query the Definition of System views

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 : 
SELECT * FROM SYS.TABLES

Execute the following query in SSMS to get the Definition for  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