Quickly generate a database dictionary

Quickly generate a database dictionary

SELECT TableName = case when c. column_id = 1 then o. name ELSE n'' END, TableDesc = ISNULL (case when c. column_id = 1 then ptb. [value] END, n''), Column_id = C. column_id, ColumnName = C. name, PrimaryKey = ISNULL (IDX. primaryKey, n''), [IDENTITY] = case when c. is_identity = 1 THEN n' √ 'else n' END, Computed = case when c. is_computed = 1 THEN n' √ 'else n'' END, Type = T. name, Length = C. max_length, Precision = C. precision, Scale = C. scale, NullAble = case when c. is_nullable = 1 THEN n' √ 'else n' END, [Default] = ISNULL (D. definition, n''), ColumnDesc = ISNULL (PFD. [value], n''), IndexName = ISNULL (IDX. indexName, n''), IndexSort = ISNULL (IDX. sort, n''), Create_Date = O. create_Date, Modify_Date = O. modify_dateFROM sys. columns c inner join sys. objects o on c. [object_id] = O. [object_id] and o. type = 'U' and o. is_ms_shipped = 0 inner join sys. types t on c. user_type_id = T. user_type_id left join sys. default_constraints d on c. [object_id] = D. parent_object_id and c. column_id = D. parent_column_id and c. default_object_id = D. [object_id] left join sys. extended_properties pfd on pfd. class = 1 and c. [object_id] = PFD. major_id and c. column_id = PFD. minor_id -- and pfd. name = 'caption '-- name of the description corresponding to the field description (multiple descriptions of different names can be added for one field) left join sys. extended_properties ptb on ptb. class = 1 and ptb. minor_id = 0 and c. [object_id] = PTB. major_id -- and pfd. name = 'caption '-- Description name corresponding to the table description (multiple descriptions with different names can be added to a table) left join -- index and primary key information (select idxc. [object_id], IDXC. column_id, Sort = CASE INDEXKEY_PROPERTY (IDXC. [object_id], IDXC. index_id, IDXC. index_column_id, 'isscending') WHEN 1 THEN 'desc' WHEN 0 THEN 'asc 'else' 'end, PrimaryKey = case when idx. is_primary_key = 1 THEN n' √ 'else n'' END, IndexName = IDX. name FROM sys. indexes idx inner join sys. index_columns idxc on idx. [object_id] = IDXC. [object_id] and idx. index_id = IDXC. index_id left join sys. key_constraints kc on idx. [object_id] = KC. [parent_object_id] and idx. index_id = KC. unique_index_id inner join -- only 1st index information (SELECT [object_id], Column_id, index_id = MIN (index_id) FROM sys. index_columns group by [object_id], Column_id) idxcuq on idxc. [object_id] = IDXCUQ. [object_id] and idxc. column_id = IDXCUQ. column_id and idxc. index_id = IDXCUQ. index_id) idx on c. [object_id] = IDX. [object_id] and c. column_id = IDX. column_id -- where o. name = n' table to be queried '-- if only the specified table is queried, add this condition order by o. name, C. column_id -- select * from sys. databases

How is the SQL database data dictionary generated?

Tools such as Visio and PowerDesigner support reverse engineering and can generate data models directly.
 
How to quickly create a data dictionary

In our work, not all of us have VISIO and other database design software. We often directly design the database in the SQL Enterprise Manager, and then add the database dictionary, to facilitate future work and management. Sometimes I am very upset because we have to describe the data fields twice, which is troublesome. However, not all of this. Not long ago, I found a better method: (using my table creation as an example) 1. Open the Enterprise Manager. 2. Right-click the link and create a new link. Select cancel when you select a table. 3. Right-click the link, select new table, and enter the name we want. (Is the method different from the traditional one?) 4. Right-click the newly created table, and then select "Table View" and "Modify custom ". 5. Select the columns we want in the pop-up dialog box. 6. OK. 7. Of course, we enter the data table structure and description in our minds. When we want to generate the data table dictionary, We will select these columns, CTRL + C, then go to Excel and press CTRL + V. If you want to export the WORD, select the desired cell in Excel, CTRL + C to the WORD, CTRL + V8, and of course OK! Let's take a look at our results!