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:

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:

  1. CREATE DATABASE DB_SystemViews
  2. GO
  4. CREATE TABLE tbl_defaultValues
  5. (
  6. Id INT IDENTITY(1,1) not null
  7. )
  9. GO
  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
  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
  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
  4. CREATE TRIGGER trg_parentTrigger ON tbl_parent FOR INSERT
  5. AS
  6. INSERT INTO dbo.tbl_defaultValues default values
  8. GO
  10. CREATE TRIGGER trg_childTrigger ON tbl_child FOR INSERT
  11. AS
  12. INSERT INTO dbo.tbl_defaultValues default values
  14. GO
If we want to see the triggers in a database DB_SystemViews.
  1. USE DB_SystemViews
  2. GO
  4. SELECT
  5. name[TriggerName],
  6. type_desc[TriggerType],
  7. create_date[TriggerCreatedDate],
  8. modify_date[TriggerModifiedDate]
  9. FROM sys.triggers

triggers

  1. USE DB_SystemViews
  2. GO
  4. --disable the trigger
  5. DISABLE TRIGGER trg_childTrigger ON dbo.tbl_child
  6. GO
  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
  4. CREATE VIEW vParent
  5. AS
  6. select * from tbl_parent
  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
  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
  4. CREATE PROC sp_parent
  5. AS
  6. SELECT * FROM tbl_parent
  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