查看SQL表结构信息

SELECT 表名 = (CASE WHEN b.colorder=1 THEN a.name
                  ELSE ''
             END),
--表说明=( case when b.colorder=1 then c.value else '' end),
    b.colid 序号,b.name 字段名,
    标识 = CASE WHEN COLUMNPROPERTY(b.id,b.name,'IsIdentity')=1 THEN '√'
              ELSE ''
         END,
    主键 = CASE WHEN EXISTS ( SELECT 1
                              FROM sys.sysobjects
                              WHERE xtype='PK' AND parent_obj=b.id AND name IN (
                                SELECT name
                                  FROM sys.sysindexes
                                  WHERE indid IN (
                                    SELECT indid
                                      FROM sys.sysindexkeys
                                      WHERE id=b.id AND colid=b.colid)) )
              THEN '√'
              ELSE ''
         END,类型 = d.name,b.length 占用字节,
    长度 = COLUMNPROPERTY(b.id,b.name,'Precision'),
    允许空 = CASE WHEN COLUMNPROPERTY(b.id,b.name,'AllowsNull')=1 THEN '√'
               ELSE ''
          END,小数位数 = COLUMNPROPERTY(b.id,b.name,'Scale'),
    默认值 = CASE WHEN e.text IS NULL THEN ''
               ELSE e.text
          END,字段说明 = ISNULL(f.value,'')
  FROM sys.sysobjects a
    INNER  JOIN sys.syscolumns b
    ON a.id=b.id AND a.xtype='U' AND a.name<>'dtproperties'
    LEFT JOIN sys.extended_properties c
    ON a.id=c.major_id AND c.minor_id=0
    LEFT JOIN sys.systypes d
    ON b.xusertype=d.xusertype
    LEFT JOIN sys.syscomments e
    ON e.id=b.cdefault
    LEFT JOIN sys.extended_properties f
    ON b.id=f.major_id AND b.colid=f.minor_id
  WHERE a.name='XXXX'
  ORDER BY b.colid --a.name='XXXX'表示查看XXX表的结构信息

 

--sp_helpconstraint   PRD_OUTSOURCE_CONTRACT
--
--sp_help  PRD_OUTSOURCE_CONTRACT
--
--sp_MShelpcolumns PRD_OUTSOURCE_CONTRACT
--
--select   *   from   information_schema.columns   where   table_name= 'PRD_OUTSOURCE_CONTRACT'

/*
--ORACLE数据库使用USER_TAB_COLUMNS表
select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from USER_TAB_COLUMNS

desc 表名

describe 表名

*/

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 SELECT   

                            (case when a.colorder=1 then d.name else '' end) as TableName,

                                 a.colorder as ColIndex,  

                                 a.name as ColName,  

                                 (case when COLUMNPROPERTY( a.id,a.name, 'IsIdentity' )=1 then '√' else '' end) as IsIdentity,  

                                 (case when (SELECT count(*) FROM sysobjects--查询主键 

                                                 WHERE (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 syscolumns  

                                                                     WHERE (id = a.id) AND(name = a.name))  

                                                     )))))   

                                     AND(xtype = 'PK' ))>0 then '√' else '' end) as IsPK,--查询主键END  

                            b.name as DataType,  

                            a.length as ByteNum,  

                            COLUMNPROPERTY(a.id,a.name,'PRECISION' ) as    ColLength,  

                            isnull(COLUMNPROPERTY(a.id,a.name,'Scale' ),0) as Scale,  

                            (case when a.isnullable=1 then '√' else '' end) as Isnullable,  

                            isnull(e.text,'' ) as DefaultValue,  

                            isnull(g.[value],'' ) AS ColDesc   

                            FROM syscolumns a left join systypes b   

                            on a.xtype=b.xusertype  

                            inner join sysobjects d   

                            on a.id=d.id and d.xtype='U' and d.name<> 'dtproperties'   

                            left join syscomments e  

                            on a.cdefault=e.id  

                            left join sys.extended_properties g  

                            on a.id=g.major_id AND a.colid = g.minor_id