SqlServer查询表名的备注

SqlServer查询表名的备注（查询表名描述MS_Description）查询表名描述MS_Description?12345SELECT表名,ds.value描述

FROMsys.extended_propertiesds

LEFTJOINsysobjectstbsONds.major_id=tbs.id

WHERE

ds.minor_id=0and

='ScheduleRecords';--表名添加表的描述?1EXECUTEsp_addextendedpropertyN'MS_Description',N'菜单表',N'user',N'dbo',N'table',N'Menus',NULL,NULL;更新表的描述?1EXECUTEsp_updateextendedpropertyN'MS_Description',N'菜单表',N'user',N'dbo',N'table',N'Menus',NULL,NULL;查询表的外键?123456SELECT表名,ds.value描述

FROMsys.extended_propertiesds

LEFTJOINsysobjectstbsONds.major_id=tbs.id

WHERE

ds.minor_id=0and

='ScheduleRecords';--表名WHEREOBJECT_NAME(sysobjects.parent_obj)='TableName'SQL查询表的所有字段的备注说明?12345678910111213141516171819202122232425262728293031323334353637383940414243SELECT

TableName=CASEWHENC.column_id=1THENO.nameELSEN''END,

TableDesc=ISNULL(CASEWHENC.column_id=1THENPTB.[value]END,N''),

Column_id=C.column_id,

ColumnName=C.name,

PrimaryKey=ISNULL(IDX.PrimaryKey,N''),

[IDENTITY]=CASEWHENC.is_identity=1THENN'√'ELSEN''END,

Computed=CASEWHENC.is_computed=1THENN'√'ELSEN''END,

Type=T.name,

Length=C.max_length,

Precision=C.precision,

Scale=C.scale,

NullAble=CASEWHENC.is_nullable=1THENN'√'ELSEN''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_dateFROMsys.columnsCINNERJOINsys.objectsOONC.[object_id]=O.[object_id]

ANDO.type='U'ANDO.is_ms_shipped=0INNERJOINsys.typesTONC.user_type_id=T.user_type_idLEFTJOINsys.default_constraintsDONC.[object_id]=D.parent_object_id

ANDC.column_id=D.parent_column_idANDC.default_object_id=D.[object_id]LEFTJOINsys.extended_propertiesPFDONPFD.class=1

ANDC.[object_id]=PFD.major_idANDC.column_id=PFD.minor_id--ANDPFD.name='Caption'--字段说明对应的描述名称(一个字段可以添加多个不同name的描述)棋牌评测网LEFTJOINsys.extended_propertiesPTBONPTB.class=1

ANDPTB.minor_id=0ANDC.[object_id]=PTB.major_id--ANDPFD.name='Caption'--表说明对应的描述名称(一个表可以添加多个不同name的描述)LEFTJOIN--索引及主键信息

(

SELECT

IDXC.[object_id],

IDXC.column_id,

Sort=CASEINDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')

WHEN1THEN'DESC'WHEN0THEN'ASC'ELSE''END,

PrimaryKey=CASEWHENIDX.is_primary_key=1THENN'√'ELSEN''END,

IndexName=IDX.Name

FROMsys.indexesIDX

INNERJOINsys.index_columnsIDXCONIDX.[object_id]=IDXC.[object_id]

ANDIDX.index_id=IDXC.index_id

LEFTJOINsys.key_constraintsKCONIDX.[object_id]=KC.[parent_object_id]

ANDIDX.index_id=KC.unique_index_id

INNERJOIN--对于一个列包含多个索引的情况,只显示第1个索引信息

(

SELECT[object_id],Column_id,index_id=MIN(index_id)

FROMsys.index_columns捕鱼游戏

GROUPBY[object_id],Column_id

)IDXCUQONIDXC.[object_id]=IDXCUQ.[object_id]

ANDIDXC.Column_id=IDXCUQ.Column_id

ANDIDXC.ind