SQL Server: System Views

System Views

When we create a new Database then SQL Server automatically creates System Views.

The following are a few System Views:

  1. sys.databases
  2. sys.tables
  3. sys.triggers
  4. sys.views
  5. sys.procedures



database

systemViews

sys.databases view

The sys.databases view:

  • Returns all the databases (system databases and user defined) names, database created date, user access description and other information of the database.

  • Here we show only the Name, created date and user access description.

SELECT name[DBName],create_date[CreatedDate],user_access_desc[UserAccessDescription] FROM sys.databases

system database

sys.tables view

The sys.tables view returns all the tables information (like table name, type of table, created date and modified date and so on) in a database:

  • Create database name as “DB_SystemViews”.

  • Create tables(tbl_defaultValues, tbl_parent and tbl_child) in that database.

 

  1. CREATE DATABASE DB_SystemViews  
  2. GO  
  3.   
  4. CREATE TABLE tbl_defaultValues  
  5. (     
  6.     Id INT IDENTITY(1,1) not null  
  7. )  
  8.   
  9. GO  
  10.   
  11. CREATE TABLE tbl_parent  
  12. (  
  13.     Id INT IDENTITY(1,1) CONSTRAINT PK_tbl_parent_Id PRIMARY KEY,  
  14.     Name VARCHAR(50)  
  15. )  
  16. GO  
  17.   
  18. CREATE TABLE tbl_child  
  19. (     
  20.     Id INT IDENTITY(1,1) PRIMARY KEY,  
  21.     Name VARCHAR(50),  
  22.     ParentId INT CONSTRAINT FK_tbl_parent_tbl_child_ParentId FOREIGN KEY REFERENCES tbl_parent(Id)  
  23. )  
After creating the table if we want to see the created tables in the database DB_SystemViews.
  1. USE DB_SystemViews  
  2. GO  
  3. SELECT   
  4.         name[TableName],  
  5.         type_desc[Description],  
  6.         create_date[TableCreatedDateWithTime],  
  7.         modify_date[TableModifeidDateWithTime]  
  8.  FROM sys.tables  
  9.   
  10. GO  

The preceding query returns DB_SystemViews database tables.

table

sys.triggers view

The sys.triggers view returns all the triggers information (like trigger name, type of trigger, created date and modified date and so on) in the database tables.

Create simple triggers for two different tables (tbl_parent and tbl_child).

  1. USE DB_SystemViews  
  2. GO  
  3.   
  4. CREATE TRIGGER trg_parentTrigger ON tbl_parent FOR INSERT  
  5. AS  
  6. INSERT INTO dbo.tbl_defaultValues default values  
  7.   
  8. GO  
  9.   
  10. CREATE TRIGGER trg_childTrigger ON tbl_child FOR INSERT  
  11. AS  
  12. INSERT INTO dbo.tbl_defaultValues default values  
  13.   
  14. GO  
If we want to see the triggers in a database DB_SystemViews.
  1. USE DB_SystemViews  
  2. GO  
  3.   
  4. SELECT   
  5.     name[TriggerName],  
  6.     type_desc[TriggerType],  
  7.     create_date[TriggerCreatedDate],  
  8.     modify_date[TriggerModifiedDate]   
  9. FROM sys.triggers  

triggers

 

  • This view also shows trigger tables and trigger status also.
  • Status indicates whether the trigger is in enabled or disabled mode, it returns a Boolean value.
  • 0: indicates the trigger is in Enabled mode
  • 1: indicates the trigger is in Disabled mode

 

  1. USE DB_SystemViews  
  2. GO  
  3.   
  4. --disable the trigger  
  5. DISABLE TRIGGER trg_childTrigger ON dbo.tbl_child  
  6. GO  
  7.   
  8. SELECT   
  9.     name[TriggerName],  
  10.     OBJECT_NAME(parent_id)[TableName],  
  11.     is_disabled[Status]   
  12. FROM sys.triggers  
  13. GO  

disable the trigger

sys.views view

The sys.views view returns all the views information (like view name, type of view, created date and modified date and so on) in a database.

The following shows how to create a simple view:

  1. USE DB_SystemViews  
  2. GO  
  3.   
  4. CREATE VIEW vParent  
  5. AS  
  6.     select * from tbl_parent  
  7.       
  8. GO  
If we want to see the views in a database DB_SystemViews.
  1. USE DB_SystemViews  
  2. GO  
  3. SELECT   
  4.         name[ViewName],  
  5.         type_desc[Description],  
  6.         create_date[ViewCreatedDateWithTime],  
  7.         modify_date[ViewModifeidDateWithTime]  
  8.  FROM sys.views  
  9.   
  10. GO  

query

sys.procedures view

The sys.procedures view returns all the procedures information (like procedure name, type of procedure, created date and modified date and so on) in a database.

The following shows how to create a simple proc:

  1. USE DB_SystemViews  
  2. GO  
  3.   
  4. CREATE PROC sp_parent  
  5. AS  
  6.  SELECT * FROM tbl_parent  
  7.   
  8. GO  
If we want to see the procedures in a database DB_SystemViews.
  1. USE DB_SystemViews  
  2. GO  
  3. SELECT   
  4.         name[ProcName],  
  5.         type_desc[Description],  
  6.         create_date[ProcCreatedDateWithTime],  
  7.         modify_date[ProcModifeidDateWithTime]  
  8.  FROM sys.procedures  

SystemViews image

 

Up Next
    Ebook Download
    View all
    Learn
    View all