|
|
-
|
|
sys.server_role_members contains the membership of each server principal
with server roles. It returns a pair of role_principal_id and member_principal_id
for each membership as below:
-- © 2011 – Vishal (http://SqlAndMe.com)
SELECT role_principal_id, member_principal......
|
|
-
|
|
While sys.server_principals contains all logins/roles created on the server. sys.database_principals contains all users/roles in the current database. Some of the columns returned by sys.database_principals are as below:
1. Name – name of the principal – user/role name,
2. Principal_id......
|
|
-
|
|
sys.identity_columns contains a list of all identity columns in the database, some of the columns returned by sys.identity_columns are as below:
1. Object_id – object id of the parent table,
2. Name – column name for the identity column,
3. Column_id – id of the columns in the t......
|
|
-
|
|
sys.computed_columns returns all columns that are computed in the database, below
are some of the columns returned by sys.computed_columns:
1. Object_id – object id of the table to which the column belongs,
2. Name – name of the computed column,
3. Column_......
|
|
-
|
|
While
sys.check_constraints returns all CHECK constraints in the database,
sys.key_constraints and sys.default_constraints returns primary/unique key constraints
and DEFAULT constraints respectively.
some of the columns returned by sys.key_constraints are:
1. ......
|
|
-
|
|
sys.sql_modules returns a row for each function, procedure, trigger, view and rules defined in the database. Below are some of the columns returned by sys.sql_modules:
1. Object_ID – ID of the object in the database,
2. Definition – module text,
3. Uses_ansi_null – 1 if module w......
|
|
-
|
|
sys.foreign_keys contains a list of all FOREIGN KEYS in the database. It contains one row per FOREIGN KEY. Below is a list of some columns returned by this view:
1. Name – FOREIGN KEY name,
2. Schema_ID – Schema ID of the parent object,
3. Parent_Object_ID – ID of the object to ......
|
|
-
|
|
sys.views returns a row for each view in the database, sys.views only returns rows for which the user owns or has been granted permission for. Below are some of the columns returned by sys.views.
1. Name – Give name of the view,
2. Schema_id – ID of schema to which the view belongs,
3......
|
|
-
|
|
You can use the sys.dm_db_file_space_usage dynamic management view to monitor the spaced used by tempdb for storing user/internal objects and version store.
sys.dm_db_file_space_usage has following columns:
1. database_id – will always be 2, database id of tempdb.
2. unallocated_extent_page......
|
|
-
|
|
While you can use sys.dm_db_file_space_usage to get the free space available in tempdb. you can use sys.dm_db_session_space_usage to get the used space by each session.
sys.dm_db_session_space_usage contains following columns:
1. session_id – session_id
2. database_id – will always be......
|
|