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 表“的形式,大大减轻了工作量