Get rid of the data dictionary so that the SQL Server database has a self-description

We all know that assembly has metadata and can be "self-described ". When we experience this advantage, have we ever wondered when the information in the database is also self-described?

I think SQL Server has extended attributes and SQL Server's "metadata" can be found in some system tables and system views, so why don't I use these extended attributes to describe tables, views, fields, and so on in the database, saving the trouble of manually maintaining external data dictionaries such as Excel format?

The following is my preliminary practice, which may not be perfect. I hope you can give me a good result.

First, for fields in a data table, because when you use SSMs to add fields to a table, you can enter a description for an attribute. Therefore, it is very simple to obtain the description information, I implemented the following view:

Create view [DBO]. [doc_vwcolumns]
As
-- Description of data table fields
-- Author: rickylin, http://Ricky81317.cnblogs.com/
Select T. table_name, C. column_name, C. data_type, C. character_maximum_length, C. is_nullable, P. [value] As columndescription
From information_schema.tables t
Inner join information_schema.columns C on C. table_name = T. table_name
Left join SYS. extended_properties P on p. major_id = object_id (T. table_name) and P. minor_id = C. ordinal_position and P. [name] = 'Ms _ description'
Where T. table_type = 'base table'
-- Order by T. table_name, C. ordinal_position

 

By querying this view, you can get descriptions of fields in each data table. Because it is a view, you can add the where condition, filter the field information of the expected table from this view.

 

When you create a database for tables, views, stored procedures, functions, and so on, there is no explicit "Description". However, after creating these database objects, you can select the object in SSMs and right-click --> attribute to find the "extended attribute" on the left of the attribute dialog box ", add an extension attribute to describe the object information. I am using the extended attributes added by the objectdescription name. After the attributes are added, click OK. Then you can use the following view for query:

Create view [DBO]. [doc_vwobjects]
As
-- Description of database objects
-- Author: rickylin, http://Ricky81317.cnblogs.com/
Select T. [object_id] As objectid, T. [name] As objectname, T. [type] As objecttype, P. [value] As objectdescription
From SYS. Objects t
Left join SYS. extended_properties P on p. major_id = T. [object_id]
Where p. [name] = 'objectdescription'

Is it easy?

In this way, we can establish a self-description method for our database, and maintain and query the description information in a more convenient way. Whether we back up or detach the database, these descriptions are all taken along with the database files in the database (this is also the advantage of self-description), and there is no need to attach additional file description documents. Even if you want a database dictionary in the latest Excel format, you only need to query the result and copy and paste it into the Excel file.