SQL查询所有表

文章来自：博客园-达奇

查询所有表 
 1 SELECT   
 2 TableName               =   CASE   WHEN   A.COLORDER=1   THEN   D.NAME   ELSE   D.NAME   END, 
 3 FieldID       =   A.COLORDER, 
 4 FieldName           =   A.NAME, 
 5 Mark               =   CASE   WHEN   COLUMNPROPERTY(   A.ID,A.NAME, 'ISIDENTITY ')=1   THEN   '√ 'ELSE   ' '   END, 
 6 Prik               =   CASE   WHEN   EXISTS(SELECT   1   FROM   SYSOBJECTS   WHERE   XTYPE= 'PK '   AND   PARENT_OBJ=A.ID   AND   NAME   IN   ( 
 7 SELECT   NAME   FROM   SYSINDEXES   WHERE   INDID   IN( 
 8 SELECT   INDID   FROM   SYSINDEXKEYS   WHERE   ID   =   A.ID   AND   COLID=A.COLID)))   THEN   '√ '   ELSE   ' '   END, 
 9 Type               =   B.NAME
10 FROM   
11 SYSCOLUMNS   A 
12 LEFT   JOIN   
13 SYSTYPES   B   
14 ON   
15 A.XUSERTYPE=B.XUSERTYPE 
16 INNER   JOIN   
17 SYSOBJECTS   D   
18 ON   
19 A.ID=D.ID     AND   D.XTYPE= 'U '   AND     D.NAME <> 'DTPROPERTIES ' 
20 LEFT   JOIN   
21 SYSCOMMENTS   E   
22 ON   
23 A.CDEFAULT=E.ID 
24 LEFT   JOIN   
25 sys.extended_properties   G   
26 ON   
27 A.ID=G.major_id   AND   A.COLID=G.minor_id     
28 LEFT   JOIN   
29 sys.extended_properties   F   
30 ON   
31 D.ID=F.major_id   AND   F.minor_id=0 
32 ORDER   BY   
33 A.ID,A.COLORDER
另一个查询所有表 
 1 select 
 2 [表名]=c.Name,
 3 [表说明]=isnull(f.[value],''),
 4 [列名]=a.Name,
 5 [列序号]=a.Column_id,
 6 [标识]=case when is_identity=1 then '√' else '' end,
 7 [主键]=case when exists(select 1 from sys.objects x join sys.indexes y on x.Type=N'PK' and x.Name=y.Name 
 8 join sysindexkeys z on z.ID=a.Object_id and z.indid=y.index_id and z.indid in (0,1) and z.Colid=a.Column_id)
 9 then '√' else '' end,
10 [类型]=b.Name,
11 [字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G' 
12 when b.Name='xml' then '2^31-1字节/2G'
13 else rtrim(a.[max_length]) end,
14 [长度]=case when ColumnProperty(a.object_id,a.Name,'Precision')=-1 then '2^31-1'
15 else rtrim(ColumnProperty(a.object_id,a.Name,'Precision')) end,
16 [小数]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),
17 [是否为空]=case when a.is_nullable=1 then '√' else '' end,
18 [列说明]=isnull(e.[value],''),
19 [默认值]=isnull(d.text,'') 
20 from 
21 sys.columns a
22 left join
23 sys.types b on a.user_type_id=b.user_type_id
24 inner join
25 sys.objects c on a.object_id=c.object_id and c.Type='U'
26 left join
27 sys.syscomments d on a.default_object_id=d.ID
28 left join
29 sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1 
30 left join
31 sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1

 

posted @ 2013-01-22 12:51  学海无涯1999  阅读(128)  评论(0)    收藏  举报