SELECT ( Case whenA.colorder=1 ThenD.nameElse "' End) Table name, A.colorder field ordinal, a.name field name, G.[value] asField Description fromSyscolumns A Left Joinsystypes b onA.xtype=B.xusertypeInner Joinsysobjects D ona.ID=D.id andD.xtype='U' andD.name<>'dtproperties' Left Joinsys.extended_properties G ona.ID=g.major_id andA.colid=g.minor_idWHERED.[name] <>'Table_desc' --the name of the table you want to view, comment out, view field information for all tables in the current databaseOrder byA.id,a.colorder--Creating tables and descriptive informationCreate TableTable (A1varchar(Ten), A2Char(2))--add descriptive information to a tableEXECUTESp_addextendedproperty N'ms_description','Personnel Information Sheet'N'User'N'dbo'N'Table'N'Table',NULL,NULL--Add descriptive information for field A1EXECUTESp_addextendedproperty N'ms_description','name'N'User'N'dbo'N'Table'N'Table'N'column'N'A1'--Add descriptive information for field A2EXECUTESp_addextendedproperty N'ms_description','Sex'N'User'N'dbo'N'Table'N'Table'N'column'N'A2'--Update the Description property of the column A1 in the table:EXECSp_updateextendedproperty'ms_description','Field 1','User'Dbo'Table','Table','column', A1--Delete the Description property of the column A1 in the table:EXECSp_dropextendedproperty'ms_description','User'Dbo'Table','Table','column', A1--Delete TestDrop TableTable
Obtaining and modifying SQL Server table field descriptions