RealWorldDevelopers
9/19/2016 - 11:19 PM

Show All Non System Objects

Show All Non System Objects

--User Triggers  
 SELECT   A.   NAME    AS   TriggerName, B.   NAME    AS   onTableNamed  
 FROM   sysobjects A,sysobjects B  
 WHERE   A.xtype   =   'TR'    AND   A.parent_obj   =   B.id  

 --User Stored Procedures  
 SELECT   [   NAME   ]   AS   StoredProcedureName  
 FROM   sysobjects  
 WHERE   xType   =    'P'   
 ORDER BY   [   NAME   ]  

 --User Functions  
 SELECT   [   NAME   ]   AS   FunctionName  
 FROM   sysobjects  
 WHERE   xType   IN   (   'FN'   ,   'TF'   )  
 ORDER BY   [   NAME   ]  

 --user created tables  
 SELECT   [   NAME   ]   AS   TableName  
 FROM   sysobjects  
 WHERE   xType   =    'U'   
 ORDER BY   [   NAME   ]  

 --user created views  
 SELECT   [   NAME   ]   AS   TableName  
 FROM   sysobjects  
 WHERE   xType   =    'V'   
 ORDER BY   [   NAME   ]  

 --indexs  
 SELECT       --SO.object_id,     
    SO.   NAME    AS   tableName,    
      CASE   SI.is_primary_key   WHEN   1   THEN    'Y'    ELSE    'N'   END   AS   PK,  
    SI.type_desc   AS   IndexType,  
    SI.   NAME    AS   IndexName,  
    SC.   NAME    AS   ColumnName    
 FROM     sys.objects SO   INNER    JOIN   
    sys.indexes SI     ON   SO.object_id   =   SI.object_id     INNER    JOIN   
    sys.index_columns SIC   ON   SIC.object_id   =   SI.object_id     AND   SIC.index_id   =   SI.index_id   INNER    JOIN   
    sys.columns SC     ON   SC.object_id   =   SIC.object_id     AND   SC.column_id   =   SIC.column_id    
 WHERE     SO.type_desc     =    'USER_TABLE'      
 ORDER BY   SO.   NAME   , SI.   NAME  

 --SELECT * FROM sysobjects  ORDER BY xtype