SQL Server:ms_description

--sql Server table description and field description Add, delete, change, Query--sql Server 2000 system table sysproperties invalid in SQL 2008 Today, inadvertently found on the Internet SQL Server has an extended attribute system table sysproperties, because only to contact the MSSQL2005 and later versions, in the production library 2008 and online documents are not found on the system table,  It was later discovered that the system table was replaced by another system table Sys.extended_properites after version 2005. --select * from sys.extended_properites where major_id = object_id and name = ' ms_description '--Test:--Create TABLE and description information Geovindu (duname varchar), Isname char (2))--Add descriptive information to the table execute sp_addextendedproperty n ' ms_description ', ' Personnel information table ', n ' User ', n ' dbo ', n ' table ', n ' Geovindu ', NULL, null--for field duname Add description info Execute sp_addextendedproperty N ' ms_description ', ' name ', n ' user ', n ' dbo ', n ' table ', n ' Geovindu ', n ' column ', n ' duname '--Add description information for field Isname execute sp_addextendedproperty N ' Ms_ Description ', ' Gender ', n ' user ', n ' dbo ', n ' table ', n ' Geovindu ', n ' column ', n ' isname '--Update the Description property of the column duname in the table: EXEC sp_ Updateextendedproperty ' ms_description ', N ' poly ', ' user ', dbo, ' table ', ' Geovindu ', ' column ', Dunameexec sp_ Updateextendedproperty n ' ms_description ', ' tu ', n ' user ', n ' dbo ', n ' table ', n ' Geovindu ', n ' colUmn ', N ' duname '--Delete the description attribute of the column duname in the table: EXEC sp_dropextendedproperty ' ms_description ', ' user ', dbo, ' table ', ' Geovindu ', ' Column ', duname--as for the query, SQL Server has a system function fn_listextendedproperty ():---Get a description of a field select * From:: fn_ Listextendedproperty (NULL, ' user ', ' dbo ', ' table ', ' Geovindu ', ' column ', default)--other variables, according to your request, you can write, as long as the table name for your where objname = ' Field name--Delete test drop table Geovindugo---1. Note Description for all fields of the SQL query table select Sysobjects.name as table_name, syscolumns. Id, syscolumns.name as column_name,systypes.name as Data_type, syscolumns.length as Character_maximum_length, Sys.extended_properties. [Value] As Column_description, Syscomments.text as column_default,syscolumns.isnullable as is_nullable from syscolumns INNER JOI  N systypes on syscolumns.xtype = Systypes.xtype left joins sysobjects on syscolumns.id = Sysobjects.id left OUTER JOIN sys.extended_properties on (sys.extended_properties.minor_id = Syscolumns.colid and SYS.EXTENDED_PROPERTIES.M ajor_id = syscolumns.id) left OUTER JOIN syscomments onSyscolumns.cdefault = syscomments.id where syscolumns.id in (the SELECT ID from SYSOBJECTS where xtype = ' U ') and (systy Pes.name <> ' sysname ') ORDER by Syscolumns.colid--2.sql Note Description of all fields in the query table select (case when A.colorder=1 then D.name el Se ' end ' n ' table name ', A.colorder n ' field ordinal ', a.name n ' field name ', (case is ColumnProperty (a.id,a.name, ' isidentity ') =1 then ' √ ' Else '            ' End ' N ' id ', (case when (select COUNT (*) from sysobjects WHERE (the 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 sys Columns 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 (columnprope Rty (a.id,a.name, ' scale '),0) as n ' decimal place ', (case time a.isnullable=1 then ' √ ' else ' end) n ' Allow null ', IsNull (E.text, ') n ' default value ', IsNull (G.[value], ') as N ' Field description ' from syscolumns a left joins systypes B on A.xtype=b.xusertype inner joins sysobjects D on A.id=d.id and d.xtype= ' U ' a nd d.name<> ' dtproperties ' left joins syscomments E on a.cdefault=e.id left joins Sys.extended_properties G on A.ID=G.M ajor_id and A.colid = g.minor_id ORDER by object_name (a.id), a.colorder--3. Note Description for all fields of SQL 2005 query table Select Tablename=case when c.column_id=1 then o.name ELSE N ' end,tabledesc=isnull (case when C.colum N_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 and 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 cinner JOIN sys.objects OON C.[object_ Id]=o.[object_id]and o.type= ' U ' and O.is_ms_shipped=0inner join Sys.types TON c.user_type_id=t.user_type_idleft Join Sys.default_constraints DON C.[object_id]=d.parent_object_idand C.column_id=d.parent_column_idand C.default_object _id=d.[object_id]left JOIN sys.extended_properties Pfdon Pfd.class=1 and c.[object_id]=pfd.major_id and C.column_id= pfd.minor_id--and Pfd.name= ' Caption '--The description name of the field description (a field can be added with multiple different name descriptions) left JOIN sys.extended_properties Ptbon Ptb.class=1 and Ptb.minor_id=0 and c.[object_id]=ptb.major_id--and Pfd.name= ' Caption '--table describes the corresponding description name ( A table can add multiple descriptions of different name) 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, ' isdescending ') when 1 and ' DESC ' when 0 Then ' ASC ' ELSE ' End,primarykey =case when Idx.is_primary_key=1 then n ' √ ' ELSE n ' End,indexname=idx. Namefrom sys.indexes idxinner JOIN sys.index_columns idxcon IDX. [OBJECT_ID]=IDXC. [OBJECT_ID] and Idx.index_id=idxc.index_idleft JOIN sys.key_constraints KCON IDX. [Object_id]=kc.[parent_object_id]and Idx.index_id=kc.unique_index_idinner JOIN--only the 1th index information is displayed for a column that contains multiple indexes (SELECT [object_id], column_id, Index_id=min (index_id) from Sys.index_columnsgroup by [object_id], column_id) IDXCUQON IDXC. [Object_id]=idxcuq. [OBJECT_ID] and IDXC. Column_id=idxcuq. Column_idand idxc.index_id=idxcuq.index_id) Idxon c.[object_id]=idx. [OBJECT_ID] and c.column_id=idx.column_id--where o.name=n ' Geovindu '--if only the specified table is queried, add this condition to order by o.name,c.column_id

SQL Server:ms_description