将中所有表的列信息显示出来

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

　正在作一个关于SQLSERVER导入文件的程序，要读取数据库中的列的信息，从网上找了很多资料，终于总结出来比较理想的sql语句，执行后返回的列分别是：表名、列名、列类型、列长度、列描述、是否主键，语句如下：

1　SelectSysobjects.NameAsTb_name,Syscolumns.NameAsCol_name,Systypes.NameAsCol_type,Syscolumns.LengthAsCol_len,Isnull(Sysproperties.Value,Syscolumns.Name)AsCol_memo,
2　CaseWhenSyscolumns.NameIn
3　(Select主键=A.Name
4　FromSyscolumnsA
5　InnerJoinSysobjectsBOnA.Id=B.IdAndB.Xtype='U'AndB.Name<>'Dtproperties'
6　WhereExists(Select1FromSysobjectsWhereXtype='Pk'AndNameIn(
7　SelectNameFromSysindexesWhereIndidIn(
8　SelectIndidFromSysindexkeysWhereId=A.IdAndColid=A.Colid
9　)))
10　AndB.Name=Sysobjects.Name
11　)
12　Then1Else0EndAsIs_key
13
14　FromSysobjects,Systypes,Syscolumns
15　LeftJoinSyspropertiesOn(Syscolumns.Id=Sysproperties.IdAnd
16　Syscolumns.Colid=Sysproperties.Smallid)
17
18　Where(Sysobjects.Xtype='U'OrSysobjects.Xtype='V')
19　AndSysobjects.Id=Syscolumns.IdAndSystypes.Xtype=Syscolumns.Xtype
20　AndSystypes.Name<>'Sysname'AndSysobjects.NameLike'%'OrderBySysobjects.Name,Syscolumns.Colid

结果如图：

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>