SQL System Catalog: A Mind Map

To work with a specific set of tables and views must have a mechanism for determining the structure and attributes of the objects in any database to which they connect; in this post a mind map on System Catalog, check the first post here.

System Catalog
1 Catalog viewsCatalog views belong to the sys schema N> Because system tables cannot be referenced directly, you have to query catalog views
1.1 sys.objectsa row for each user-defined object in relation to the user’s schema Column Name = Description name = Object name object_id = Object identification number, unique within a database schema_id = ID of the schema in which the object is contained type = Object type
1.2 sys.columns
1.3 sys.database_principals
1.4 ex
 USE sample; SELECT object_id, principal_id, type FROM sys.objects WHERE name = 'employee'; 
 -- owner of the employee table SELECT sys.database_principals.name FROM sys.database_principals INNER JOIN sys.objects ON sys.database_principals.principal_id = sys.objects.schema_id WHERE sys.objects.name = 'employee' AND sys.objects.type = 'U'; 
2 (DMVs) (DMFs)Dynamic management views (DMVs) and functions (DMFs) DMVs (and DMFs) are used to access dynamic properties of the system active processes(Relationship)
2.1 dm_db_*
2.2 dm_tran_*
2.3 dm_io_*
2.4 dm_exec_*
3 Information schemaread-only views that provide information about all tables, views, and columns of Database Engine to which you have access, it primarily manages the environment of a database.
3.1 Information_schema.tables
3.2 Information_schema.columns
3.3 information_schema.referentail_constraints
4 system base tables
4.1 system information
5 Property functionsproperties of database objects, data types, or files
5.1 OBJECTPROPERTY(id, property)
5.2 COLUMNPROPERTY(id, column, property)
5.3 FILEPROPERTY(filename, property)
5.4 TYPEPROPERTY(type, property)
6 System functions
6.1 OBJECT_ID(object_name)
6.2 USER_ID([user_name])
6.3 OBJECT_NAME(object_id)
6.4 USER_NAME([user_id])
6.5 DB_ID([db_name])
6.6 DB_NAME([db_id])
6.7 INDEX_COL(table, index_id, col_id)
6.7.1 ex
  SELECT INDEX_COL('employee', 1,1) AS index_col_1 
7 System proceduresto provide many administrative and end-user tasks
7.1 sp_help
7.2 sp_depends
A1 active processes  

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s