SqlServer查询表名的备注.docxVIP

SqlServer SqlServer 查询表名的备注（查询表名描述 MS_Description） 查询表名描述 MS_Description 查询表名描述 MS_Description 1SELECT tbs.name 表名,ds.value 描述 2FROM sys.extended_properties ds 3LEFT JOIN sysobjects tbs ON ds.major_id=tbs.id 4WHERE ds.minor_id=0 and 5tbs.name=ScheduleRecords;--表名 添加表的描述 添加表的描述 1EXECUTE sp_addextendedproperty NMS_Description, N菜单表, Nuser, Ndbo, Ntable, NMenus, NULL, NULL; 更新表的描述 更新表的描述 1EXECUTE sp_updateextendedproperty NMS_Description, N菜单表, Nuser, Ndbo, Ntable, NMenus, NULL, NULL; 查询表的外键 查询表的外键 1SELECT tbs.name 表名,ds.value 描述 2FROM sys.extended_properties ds 3LEFT JOIN sysobjects tbs ON ds.major_id=tbs.id 4WHERE ds.minor_id=0 and 5tbs.name=ScheduleRecords;--表名 6WHERE OBJECT_NAME(sysobjects.parent_obj)=Table Name SQL 查询表的所有字段的备注说明 SQL 查询表的所有字段的备注说明 1SELECT 2 3 4 5 6 7 8 9 10 11 12 13 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_date 20FROM sys.columns C 21INNER JOIN sys.objects O ON C.[object_id]=O.[object_id] 22 AND O.type=U AND O.is_ms_shipped=0 23INNER JOIN sys.types T ON C.user_type_id=T.user_type_id 24LEFT JOIN sys.default_constraints D ON C.[object_id]=D.parent_object_id 25 AND C.column_id=D.parent_column_id AND 26C.default_object_id=D.[object_id] 27LEFT JOIN sys.extended_properties PFD ON PFD.class=1 28 AND C.[object_id]=PFD.major_id AND C.column_id=PFD.minor_id 29-- AND PFD.name=Caption -- 字段说明对应的描述名称(一个字段可以添加多个不 30同 name 的描述) 31LEFT JOIN sys.extended_properties PTB ON PTB.class=1 32 AND PTB.mi