sql查詢表結(jié)構(gòu)

1、sql查詢表結(jié)構(gòu)（轉(zhuǎn)）-新建一張表后，會將所有與表有關的信息寫入該數(shù)據(jù)庫下面的某張系統(tǒng)表，sysobjects, syscolumns, systypes 是其中三張SELECTSO.name as 表名,SC.name as 表列名，SC.colid as 索引,ST.name as 類型FROMsysobjects SO,-對象表syscolumns SC,-歹。名表systypes ST -數(shù)據(jù)類型表WHERESO.id = SC.idAND SO.xtype = U -類型U表示表，V表示視圖AND SO.status = 0 -加一個條件：SO.status = 0，否則會將系統(tǒng)的臨

2、時表顯示出來AND SC.xtype = ST.xusertypeORDER BYSO.name, SC.colorder-按表名、列名排序SELECTSO.name 表名，SC.name表列名，SC.colid 索引，ST.name 類型FROMsysobjects SO,-對象表syscolumns SC,-歹。名表systypes ST -數(shù)據(jù)類型表 CliqueClientWHERESO.id = SC.idAND SO.xtype = U-類型U表示表，V表示視圖AND SO.status = 0- status = 0 為非系統(tǒng)對象AND SC.xtype = ST.xuserty

3、peAND SO.name = CliqueClient-某張?zhí)囟ū鞳RDER BYSO.name, SC.colorder-按表名、列名排序查詢指定數(shù)據(jù)表中的所有列的列名：SELECT name FROM syscolumnsWHERE id = object_id(Your Table Name)ORDER BY colorder查詢指定數(shù)據(jù)表中的所有列的列名和數(shù)據(jù)類型名：SELECT C.name AS ColumnName, T.name AS ColumnTypeFROM syscolumns CLEFT JOIN systypes T ON C.xusertype = T.xuse

4、rtypeWHERE C.id = object_id(Your Table Name)索引及主鍵信息SelectTableId=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(

5、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 NVELSE N end,UQIQUE = CASE WHEN IDX.is_unique=1 THEN NVELSEN END,Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN NVELSE N END,Disabled=CASE WHEN

6、IDX.is_disabled = 1 THEN NVELSEN END,Fill_factor=IDX.fill_factor,Padded=CASE WHEN IDX.is_padded = 1 THEN NVELSE NENDFROM sys.indexes IDXINNER JOIN sys.index_columns IDXCON IDX.object_id = IDXC.object_idAND IDX.index_id = IDXC.index_idLEFT JOIN sys.key_constraints KCON IDX.object_id = KC.parent_objec

7、t_idAND IDX.index_id = KC.unique_index_idINNER JOIN sys.objects OON O.object_id = IDX.object_idINNER JOIN sys.columns CON O.object_id=C.object_idAND O.type=UAND O.is_ms_shipped = 0AND IDXC.Column_id=C.Column_id-INNER JOIN -對于一個列包含多個索引的情況，只顯示第1個索引信息-(- Select object_id, Column_id, index_id = MIN(inde

8、x_id)-FROM sys.index_columns-GROUP BY object_id, Column_id-)IDXCUQ-ON IDXC.object_id = IDXCUQ.object_id- AND IDXC.Column_id = IDXCUQ.Column_id-表結(jié)構(gòu)信息查詢SelectTableName=CASE WHEN C.column_id = 1 THEN O.name ELSE N11 END,TableDesc=ISNULL(CASE WHEN C.column_id = 1 THEN PTB.value END,Nn),Column_id=C.colum

9、n_id,ColumnName=C.name,PrimaryKey=ISNULL(IDX.PrimaryKey,N),IDENTITY = CASE WHEN C.is_identity=1 THEN NVELSEN END,Computed=CASE WHEN C.is_computed = 1 THEN NVELSE N END,Type=T.name,Length=C.max_length,Precision=C.precision,Scale=C.scale,NullAble=CASE WHEN C.is_nullable=1 THEN NVELSE NEND,Default = IS

10、NULL(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_idAND O.type=UAND O.is_ms_shipped = 0INNER JOIN sys.types TON C.user

11、_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_idLEFT JOIN sys.extended_properties PFDON PFD.class=1AND C.object_id = PFD.major_idAND C.column_id = PFD.minor_id-AND PFD.name=Caption-字

12、段說明對應的描述名稱（一個字段可以添加多個不同name的描述）LEFT JOIN sys.extended_properties PTBON PTB.class=1AND PTB.minor_id = 0AND C.object_id = PTB.major_id-AND PFD.name=Caption-表說明對應的描述名稱（一個表可以添加多個不同name的描述）LEFT JOIN-索引及主鍵信息（SelectIDXC.column_id,Sort=CASE INDEXKEY_PROPERTY(IDXC.object_id,IDXC.index_id,IDXC.index_column_id

13、,IsDescending)WHEN 1 THEN DESC WHEN 0 THEN ASC ELSE END,PrimaryKey=CASE WHEN IDX.is_primary_key=1 TH EN N/ELSE N END,IndexName=IDX.NameFROM sys.indexes IDXINNER JOIN sys.index_columns IDXCON IDX.object_id = IDXC.object_idAND IDX.index_id = IDXC.index_idLEFT JOIN sys.key_constraints KCON IDX.object_id = KC.parent_object_idAND IDX.index_id = KC.unique_index_idINNER JOIN -對于一個列包含多個索引的情況，只顯示第1 個索引信息(Select object_id, Column_id, index_id = MIN(index _id)FROM sys.index_columns)IDXCUQON IDXC.object_id =