Check some of the information on the internet but found that there are problems sticking to the code, but had to modify their own, the code is as follows:
The following code can be normal operation, is the Internet to find information finally pieced together summed up. You can add the conditions yourself. The internet seems to have a lot of types of things, here to keep for the time being only!
Copy Code code as follows:
SELECT
(case when A.colorder=1 then D.name else "end) N ' table name ',
A.colorder N ' field ordinal ',
A.name N ' field name ',
(Case when ColumnProperty (A.id,a.name, ' isidentity ') =1 then ' √ ' Else ' "end) N ' logo ',
(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 ' occupies bytes ',
ColumnProperty (a.id,a.name, ' PRECISION ') as N ' length ',
IsNull (ColumnProperty (a.id,a.name, ' Scale '), 0) as N ' decimal digits ',
(case when a.isnullable=1 then ' √ ' else ') N ' Allow null ',
IsNull (E.text, ') N ' default value ',
IsNull (G.[value], ') as N ' field description '
--into # #tx
From syscolumns a LEFT join systypes 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
Sysproperties Table of the detailed:
1, table name: SQL Server Annotation table, each database has, including table and field comments, table or column comments will be stored, no comments are not stored.
2, Field:
ID: The ID of the table or the ID of the column
Smallid: The order of the columns, sorted by
Type:3 table, 4 fields, possibly with constraints or something, haven't tested out
Name: Unified Ms_description
Value: Description
If need to revise (reprint part)
If you need to update the contents of this table need to open [allow the system directory to modify directly], otherwise can not change the system table
If this feature is not turned on, you can also use stored procedures [sp_addextendedproperty] to add \ update \ Delete tables and fields as described in the following
--Create a table
Create Table Table (A1 varchar (), a2 char (2))
--Add descriptive information to the table: the second parameter is [description] and the sixth argument is [table name]
EXECUTE sp_addextendedproperty n ' ms_description ', ' Personnel information table ', n ' user ', n ' dbo ', n ' table ', n ' tables ', NULL, NULL
--Update the description for the table: the second parameter is [description] and the sixth argument is [table name]
EXECUTE sp_updateextendedproperty ' ms_description ', n ' Adsfasfdas ', n ' user ', n ' dbo ', n ' table ', n ' tables ', NULL, NULL
--Delete descriptive information for the table: the fifth parameter is [table name]
EXEC sp_dropextendedproperty ' ms_description ', n ' user ', n ' dbo ', n ' table ', n ' table ', NULL, NULL
--Add Update Delete method of field
--Add descriptive information for field A1: the second parameter is [description] The sixth parameter is [table name] Eighth parameter is [field name]
EXECUTE sp_addextendedproperty n ' ms_description ', ' name ', n ' user ', n ' dbo ', n ' table ', n ' watch ', n ' column ', n ' A1 '
--Update Field A1 Description property: The second parameter is [description] The sixth parameter is [table name] Eighth parameter is [field name]
EXEC sp_updateextendedproperty ' ms_description ', ' field 1dd ', ' user ', dbo, ' table ', ' Tables ', ' column ', N ' A1 '
--delete Field A1 Description property: The fifth parameter is [table name] Seventh parameter is [field name]
EXEC sp_dropextendedproperty ' ms_description ', ' user ', dbo, ' table ', ' Tables ', ' column ', ' A1 '
--Delete test
Drop Table Table
