How to obtain the Chinese description of all table name table fields and fields in the database

I checked some information on the Internet but found that there were problems with the pasted code. Instead, I had to modify it myself. The Code is as follows:

The following code can run normally, and is finally pieced together to find information on the Internet. Add the conditions by yourself. There seem to be a lot of types of things on the Internet, but it is only needed from time to time!
Copy codeThe Code is as follows:
SELECT
(Case when a. colorder = 1 then d. name else ''end) n' table name ',
A. colorder n' Field Sequence Number ',
A. name n' field name ',
(Case when COLUMNPROPERTY (a. id, a. name, 'isidentity ') = 1 then' √ 'else' 'end) n' ',
(Case when (SELECT count (*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a. id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a. id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a. id) AND (name = a. name) AND
(Xtype = 'pk')> 0 then' √ 'else' end) n'primary key ',
B. name n' type ',
A. length N 'number of bytes occupied ',
COLUMNPROPERTY (a. id, a. name, 'precision ') as N 'length ',
Isnull (COLUMNPROPERTY (a. id, a. name, 'Scale'), 0) as N 'decimal ',
(Case when a. isnullable = 1 then '√ 'else' 'end) n' allow null ',
Isnull (e. text, '') n' default value ',
Isnull (g. [value], '') AS n' field description'
-- Into # tx
FROM syscolumns a left join policypes B
On a. xtype = B. xusertype
Inner join sysobjects d
On a. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties'
Left join syscomments e
On a. cdefault = e. id
Left join sys. extended_properties g
On a. id = g. major_id AND a. colid = g. minor_id
Order by object_name (a. id), a. colorder


A detailed description of the sysproperties table:

1. Table Name:'s comment table, which is available in each database, including comments for tables and fields. Comments for tables or columns are stored without comments.
2. fields:
Id: Table ID or column ID
Smallid: column order, used for sorting
Type: Table 3, field 4, which may be subject to other constraints. It has not been tested yet.
Name: Uniform MS_Description
Value: Description

If you need to modify (reprint part)

If you want to update the content of this table, you must enable [allow direct modification to the system directory]. Otherwise, the system table cannot be changed.

If this function is not enabled, you can also use the stored procedure [sp_addextendedproperty] to add \ update \ delete a table and use the following descriptions of fields:

-- Create a table
Create Table (a1 varchar (10), a2 char (2 ))

-- Add description information for the table: the second parameter is [description] and the sixth parameter is [Table name].
EXECUTE sp_addextendedproperty N 'Ms _ description', 'personnel info table ', N 'user', N 'dbo', N 'table', N 'table', NULL, NULL

-- Update the description for the table: the second parameter is [description] and the sixth parameter is [Table name].
EXECUTE sp_updateextendedproperty 'Ms _ description', N 'adsfasfdas', N 'user', N 'dbo', N 'table', N 'table', NULL, NULL

-- Delete table description: The fifth parameter is [Table name].
EXEC sp_dropextendedproperty 'Ms _ description', N 'user', N 'dbo', N 'table', N 'table', NULL, NULL

-- Add, update, and delete a field
-- Add description information for field a1: The second parameter is [description] The sixth parameter is [Table name] The eighth parameter is [field name]
EXECUTE sp_addextendedproperty N 'Ms _ description', 'name', N 'user', N 'dbo', N 'table', N 'table', N 'column ', N 'a1'

-- Update the description attribute of field a1: The second parameter is [description]. The sixth parameter is [Table name]. The eighth parameter is [field name].
EXEC sp_updateextendedproperty 'Ms _ description', 'field 1dd', 'user', dbo, 'table', 'table', 'column', n'a1'

-- Delete the description attribute of field a1: The fifth parameter is [Table name]. The seventh parameter is [field name].
EXEC sp_dropextendedproperty 'Ms _ description', 'user', dbo, 'table', 'table', 'column ', 'a1'

-- Delete test
Drop Table