查询sql数据结构sql语句

此查询数据结构的语句是本BLOG中的数据库帮助工具的关键...
由于 sql2000与sql2005的系统表名不同, 查询的字符串都不同,分别如下.....
sql2000

                 SELECT  
                 Column_id  =  c.colid,
                 ColumnName  =  c.name,  
                 PrimaryKey  =  
                  CASE   WHEN  
                  EXISTS ( SELECT   0   FROM  sysindexkeys i  WHERE  i.id  =  c.id  and  i.colid  =  c.colid)
                  THEN  N ' √ ' ELSE  N ''  
                  END ,
                  [ IDENTITY ] = CASE  
                  WHEN  C.colstat = 1  
                  THEN  N ' IDENTITY (  '  
                  + RTRIM ( IDENT_SEED (O.name)) 
                  + N '  ,  '  
                  + RTRIM ( IDENT_INCR (O.name)) 
                  + N '  ) '  
                  ELSE  N ''   END , 
                 Computed = CASE   WHEN  C.iscomputed = 1   THEN  N ' √ ' ELSE  N ''   END ,
                 type  =  t.name,
                 Length  =  c.length,
                  [ Precision ]   =  c.prec,
                 Scale  =  C.scale,
                 NullAble  =   CASE   WHEN  C.isnullable  =   1   THEN  N ' √ ' ELSE  N ''   END ,
                  [ Default ] = ISNULL (
                  CASE   WHEN  c.cdefault  >   0   THEN
                 (  SELECT   [ text ]   FROM  syscomments  WHERE  id  =  c.cdefault )
                  END
                 ,N '' ),
                 ColumnDesc  =   CASE   WHEN  
            EXISTS ( SELECT   [ value ]   FROM  sysproperties s  WHERE  s.id  =  c.id  AND  c.colid  =  s.smallid)
            THEN  ( SELECT   [ value ]   FROM  sysproperties s  WHERE  s.id  =  c.id  AND  c.colid  =  s.smallid)  ELSE   ''   END

                 FROM  syscolumns c, sysobjects o, systypes t
                 WHERE  c.id  =  o.id  AND  O.name  =  { 0 }  AND  t.xtype  =  c.xtype  order   by  c.Column_id

sql2005:

             SELECT   
            Column_id = C.column_id, 
            ColumnName = C.name, 
            PrimaryKey = ISNULL (IDX.PrimaryKey,N '' ),  
             [ IDENTITY ] = CASE  
             WHEN  C.is_identity = 1  
             THEN  N ' IDENTITY (  '  
             + RTRIM ( IDENT_SEED (O.name)) 
             + N '  ,  '  
             + RTRIM ( IDENT_INCR (O.name)) 
             + 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 
             FROM  sys.columns C 
             INNER   JOIN  sys.objects O 
             ON  C. [ object_id ] = O. [ object_id ]  
             AND  O.type = ' U '  
             AND  O.is_ms_shipped = 0  
             INNER   JOIN  sys.types T 
             ON  C.user_type_id = T.user_type_id 
             LEFT   JOIN  sys.default_constraints D 
             ON  C. [ object_id ] = D.parent_object_id 
             AND  C.column_id = D.parent_column_id 
             AND  C.default_object_id = D. [ object_id ]  
             LEFT   JOIN  sys.extended_properties PFD 
             ON  PFD.class = 1  
             AND  C. [ object_id ] = PFD.major_id 
             AND  C.column_id = PFD.minor_id 
             --  AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) 
             LEFT   JOIN  sys.extended_properties PTB 
             ON  PTB.class = 1  
             AND  PTB.minor_id = 0  
             AND  C. [ object_id ] = PTB.major_id 
             --  AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) 
             LEFT   JOIN   --  索引及主键信息 
            ( 
             SELECT  
            IDXC. [ object_id ] , 
            IDXC.column_id, 
            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 , 
            IndexName = IDX.Name 
             FROM  sys.indexes IDX 
             INNER   JOIN  sys.index_columns IDXC 
             ON  IDX. [ object_id ] = IDXC. [ object_id ]  
             AND  IDX.index_id = IDXC.index_id 
             LEFT   JOIN  sys.key_constraints KC 
             ON  IDX. [ object_id ] = KC. [ parent_object_id ]  
             AND  IDX.index_id = KC.unique_index_id 
             INNER   JOIN   --  对于一个列包含多个索引的情况,只显示第1个索引信息 
            ( 
             SELECT   [ object_id ] , Column_id, index_id = MIN (index_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 
             AND  IDXC.index_id = IDXCUQ.index_id 
            ) IDX 
             ON  C. [ object_id ] = IDX. [ object_id ]  
             AND  C.column_id = IDX.column_id 
              WHERE  O.name =  { 0 }  --  如果只查询指定表,加上此条件 
             ORDER   BY  O.name,C.column_id

{0} 的字符串应该替换为相应的数据库名....

转载于:https://www.cnblogs.com/yans/archive/2008/06/18/1224357.html