sql查询表结构.docxVIP

sql查询表结构(转)--新建一张表后，会将所有与表有关的信息写入该数据库下面的某张系统表，?sysobjects,?syscolumns,?systypes?是其中三张?SELECT???????????SO.name?as?表名,???????SC.name??as?表列名,???????SC.colid?as?索引,???????ST.name?as?类型???FROM?????????????sysobjects???SO,?--?对象表???????syscolumns???SC,?--?列名表???????systypes?????ST??--?数据类型表?WHERE??????????????SO.id?=?SC.id????AND???SO.xtype?=?U????--?类型U表示表，V表示视图????AND???SO.status?=?0?--加一个条件：SO.status?=?0，否则会将系统的临时表显示出来????AND???SC.xtype?=?ST.xusertype?ORDER?BY????????SO.name,?SC.colorder?????????--?按表名、列名排序?-----------------------------------------------------------------------------------------?SELECT?????SO.name?表名,?SC.name?表列名,?SC.colid?索引,?ST.name?类型?FROM???????sysobjects???SO,?--?对象表?syscolumns???SC,?--?列名表?systypes?????ST??--?数据类型表??CliqueClient?WHERE?????????SO.id?=?SC.id?AND???SO.xtype?=?U???????????????????--?类型U表示表，V表示视图?AND???SO.status?=?0??????????????????--?status?=?0?为非系统对象?AND???SC.xtype?=?ST.xusertype?AND???SO.name?=?CliqueClient??--?某张特定表?ORDER?BY???SO.name,?SC.colorder?????????--?按表名、列名排序?查询指定数据表中的所有列的列名：SELECT?name?FROM?syscolumns?WHERE??id?=?object_id(Your?Table?Name)?ORDER?BY?colorder查询指定数据表中的所有列的列名和数据类型名：SELECT??C.name?AS?ColumnName,?T.name?AS?ColumnType?FROM?syscolumns?CLEFT?JOIN?systypes?T?ON?C.xusertype?=?T.xusertypeWHERE?C.id?=?object_id(Your?Table?Name)?索引及主键信息?Select?????TableId=O.[object_id],????TableName=O.Name,????IndexId=ISNULL(KC.[object_id],IDX.index_id),????IndexName=IDX.Name,????IndexType=ISNULL(KC.type_desc,Index),????Index_Column_id=IDXC.index_column_id,????ColumnID=C.Column_id,????ColumnName=C.Name,????Sort=CASE?INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,IsDescending)????????WHEN?1?THEN?DESC?WHEN?0?THEN?ASC?ELSE??END,????PrimaryKey=CASE?WHEN?IDX.is_primary_key=1?THEN?N√ELSE?N?END,????[UQIQUE]=CASE?WHEN?IDX.is_unique=1?THEN?N√ELSE?N?END,????Ignore_dup_key=CASE?WHEN?IDX.ignore_dup_key=1?THEN?N√ELSE?N?END,????Disabled=CASE?WHEN?IDX.is_disabled=1?THEN?N√ELSE?N?END,????Fill_factor=IDX.fill_factor,????Padded