sql获取全部表名，并表内字段名

sql获取数据库中表

select * from sysobjects where xtype='u'       //获取当前数据库表的所有信息

结果如下所示：

select name from sysobjects where xtype='u' order by  name ASC      //获取表名并按照升序排列

sql获取数据库表内字段及相关

SELECT  
                                表名 = d.name,
                                表说明 =  isnull(f.value,''),
                                字段序号 = a.colorder,
                                字段名 = a.name,
                                标识 = case when COLUMNPROPERTY( 

a.id,a.name,'IsIdentity')=1 then '√'else '' end,
                                主键 = case when exists(SELECT 1 FROM sysobjects 

where xtype='PK' and parent_obj=a.id and name in (
                                SELECT name FROM sysindexes WHERE indid in( SELECT 

indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
                                类型 = b.name,
                                占用字节数 = a.length,
                                长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
                                小数位数 = isnull(COLUMNPROPERTY

(a.id,a.name,'Scale'),0),
                                允许空 = case when a.isnullable=1 then '√'else '' 

end,
                                默认值 = isnull(e.text,''),
                                字段说明 = isnull(g.[value],'')
                                FROM  
                                syscolumns a
                                left join  
                                systypes b  
                                on  
                                a.xusertype=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   
                                left join  
                                sys.extended_properties f
                                on  
                                d.id=f.major_id and f.minor_id=0
                                where  
                                d.name='表名'

只要按照需要，将”表名“替换为你所要查找的表的名称，就能查出表内的很多信息。

两个技术难点已经有了各自的解决方案，而我需要做的是通过程序的设计，来加强我手动敲击SQL语句的效率。

我需要查出数据库表名，表内字段名，字段描述等信息，最终通过拼接写出Sql语句，并将查出来的内容新增到另一个表中。

大致思路是以上这样，接来下贴代码：

前端设置按钮：

<asp:Button ID="btnInsert" runat="server" Text="读写" οnclick="btnInsert_Click" />

后台写方法：

protected void btnInsert_Click(object sender, EventArgs e)
    {
        string aName;
        string aQuery;
        string tbName;

        DataTable dt = SqlHelper.ExecuteDataset("select name from sysobjects where xtype='u' order by  name ASC").Tables[0];  //获取数据库中所有表名
        if (dt != null && dt.Rows.Count > 0)
        {
            for (int i = 50; i < dt.Rows.Count ; i++)                 //根据需要循环的区间进行调整
            {
                tbName = dt.Rows[i]["name"].ToString();  //获取表名
                DataTable dt1 = SqlHelper.ExecuteDataset(string.Format(@"SELECT  
                                TableDescription  =  isnull(f.value,''),
                                ColumnsName  = a.name,
                                Description  = isnull(g.[value],'')
                                FROM  
                                syscolumns a
                                left join  
                                systypes b  
                                on  
                                a.xusertype=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   
                                left join  
                                sys.extended_properties f
                                on  
                                d.id=f.major_id and f.minor_id=0
                                where  
                                d.name='" + tbName + "'")).Tables[0];       //获取表下表描述，字段名，字段描述
                StringBuilder str = new StringBuilder();  //aQuery字符串连接，创建实例
                aName = dt1.Rows[0]["TableDescription"].ToString();  //获取查询名称
                if (dt1 != null && dt1.Rows.Count > 0)
                {
                    str.Append("select" + " ");
                    for (int j = 1; j < dt1.Rows.Count; j++)
                    {
                        string ColumnsName = dt1.Rows[j]["ColumnsName"].ToString();
                        string Description = dt1.Rows[j]["Description"].ToString();
                        str.Append("a." + ColumnsName + " " + "as" + " " + Description);
                        if (j < dt1.Rows.Count - 1)
                        {
                            str.Append(",");
                        }
                    }
                    str.Append(" " + "from" + " " + tbName + " " + "a");   //进行aQuery的拼接
                }
                aQuery = str.ToString(); //获取查询语句

                /**此段为根据写好的方法将获取的每条数据中aName,aQhery,tbName插入到新的aInfo表中**/

                str.Remove(0, str.Length);//清空str内数据      
            }
        }
    }

最终的aQuery为”select 表名1 as 字段描述1，表名2 as 字段描述2，……，表名n as 字段描述n from 表“的形式，大大减轻了工作量