Sql Server表结构及索引查询器

　　因经常需要处理大数量的表，因此查看表结构、数据量、以及索引情况基本上成为了常态，无奈生产环境的数据库不让我们直接访问，因此想要了解到生产环境的数据库的表结构和索引信息变得很麻烦需要经常找DBA帮忙找，而想查看具体某个表的数据量使用 select count(1) from table方法的效率实在慢的无法忍受，正所谓求人不如求己，花了点时间自己做了个查看数据库表结构和索引的小工具顺便温习了下系统表一举两得，喜欢的朋友可以拿去用用。

工具功能很简单但胜在实用(尤其是不让开发人员用企业管理连接生产环境数据库时，简直是一大杀器)，可以提高不少效率
　　1.根据链接字符串列出服务器上所有的数据库以及表。
　　2.查看表的记录数、字段及索引(包含覆盖索引)信息。

默认界面如下

根据需要修改好数据连接串，点击 链接 自动查询出数据库信息

双击数据库加载所有的表并读取表的记录数(表名后面括号里的数字代表记录数)

双击表名称加载表的结构和索引信息

这里对列表字段做个简单翻译，以帮助英语不好的童鞋
　

表结构

ColumnName	ColumnType	ByteLength	CharLength	Scale	IsIdentity	IsNullable	Remark
字段名称	字段类型	字节长度	字符长度	小数位数	是否自增列	是否允许空	字段说明

　　

表索引

IndexName	IndexType	IsPrimaryKey	IsUnique	IsUniqueConstraint	IndexColumns	IndexIncludeColumns
索引名称	索引类型	是否主键	是否唯一	是否唯一约束	索引包含的列	覆盖索引包含的列

 

　　表结构里的Remark字段说明就是我们给列加的那个列说明,ByteLength是字段的字节长度,CharLength是字符长度，这两个主要是针对nchar,nvarchar类型的在数据库中比如定义 Name nvarchar(50)，显示的字节长度是100，字符长度才是50(nvarchar一个字符占两个字节)，不注意的话会很惨，曾经被这个玩意给害死(喜欢使用alt+F1 查看表结构信息，这时nvarchar(50)的字段类型会显示长度为 100)

　　表索引里面IndexColumns表示索引所包含的列，IndexIncludeColumns表示覆盖索引所包含的列，比如：索引IX_Test1，IndexColumns为Name,Email，IndexIncludeColumns为Address,PostCode，覆盖索引只能建立在非聚簇索引上，主要是为了解决书签查找(RID、键查找)，聚簇索引不存在书签查找所以不能创建覆盖索引，在此不再详述　　

create index IX_Test1 on Users(Name,Email) include(Address,PostCode)

恩，功能就这么简单，利用好了能在工作中省下不少事情，贴一下主要代码

public static class SqlQuery
    {

        public static List<string> GetDatabases(string connString)
        {
            string sql = "select name from sys.databases where name not in ('master','model','msdb','tempdb')";
            DataTable dt = DbHelperSQL.GetDataTable(connString, sql);
            return dt.Rows.Cast<DataRow>().Select(row => row["name"].ToString()).ToList();
        }

        public static Dictionary<string, string> GetTables(string connString, string database)
        {
            string sql = string.Format(@"select
                                        objects.name+'('+ltrim(str(rows))+')' showname,
                                        objects.name                                        
                                        from {0}.sys.objects 
                                        inner join {0}.dbo.sysindexes on objects.object_id=sysindexes.id and sysindexes.indid<=1
                                        where type='U' 
                                        order by objects.name", database);
            DataTable dt = DbHelperSQL.GetDataTable(connString.Replace("master", database), sql);
            return dt.Rows.Cast<DataRow>().ToDictionary(row => row["showname"].ToString(), row => row["name"].ToString());
        }

        public static DataTable GetColumns(string connString, string database, string tableName)
        {
            string sql = string.Format(@"select
                                        columns.name ColumnName,
                                        types.name ColumnType,
                                        columns.is_identity IsIdentity,
                                        columns.is_nullable IsNullable,
                                        cast(columns.max_length as int) ByteLength,
                                        (
                                            case 
                                                when types.name='nvarchar' and columns.max_length>0 then columns.max_length/2 
                                                when types.name='nchar' and columns.max_length>0 then columns.max_length/2
                                                when types.name='ntext' and columns.max_length>0 then columns.max_length/2 
                                                else columns.max_length
                                            end
                                        ) CharLength,
                                        cast(columns.scale as int) Scale,
                                        extended_properties.value Remark
                                        from {0}.sys.columns
                                        inner join {0}.sys.types on columns.system_type_id=types.system_type_id and columns.user_type_id=types.user_type_id
                                        left join {0}.sys.extended_properties on columns.object_id=extended_properties.major_id and columns.column_id=extended_properties.minor_id
                                        where object_id=OBJECT_ID(@tableName)
                                        order by columns.column_id", database);
            SqlParameter param = new SqlParameter("@tableName", SqlDbType.NVarChar, 100) { Value = database + ".dbo." + tableName };
            DataTable dt = DbHelperSQL.GetDataTable(connString, sql, param);
            return dt;
        }

        public static DataTable GetIndexs(string connString, string database, string tableName)
        {
            #region SQL
            string sql = string.Format(@"with IndexCTE as
                                        (
                                            select 
                                            indexes.object_id,
                                            indexes.index_id,
                                            indexes.name IndexName,
                                            indexes.type_desc IndexType,
                                            indexes.is_primary_key IsPrimaryKey,
                                            indexes.is_unique IsUnique,
                                            indexes.is_unique_constraint IsUniqueConstraint
                                            from {0}.sys.indexes
                                            where object_id =OBJECT_ID(@tableName)
                                        )
                                        ,IndexColumnTempCTE as
                                        (
                                            select 
                                            ic.object_id,
                                            ic.index_id,
                                            ic.column_id,
                                            ic.index_column_id,
                                            ic.is_included_column,
                                            cast(c.name as nvarchar(max)) columnname,
                                            CAST(null as nvarchar(max)) includekey
                                            from {0}.sys.index_columns ic
                                            inner join {0}.sys.columns c on ic.column_id=c.column_id and ic.object_id=c.object_id
                                            where ic.index_column_id=1 and ic.object_id =OBJECT_ID(@tableName)
                                            union all
                                            select
                                            ic.object_id,
                                            ic.index_id,
                                            ic.column_id,
                                            ic.index_column_id,
                                            ic.is_included_column, 
                                            case ic.is_included_column when 0 then columnname+','+c.name end,
                                            case 
                                            when ic.is_included_column = 1 and includekey is null then c.name
                                            when ic.is_included_column = 1 and includekey is not null then includekey+','+c.name
                                            end
                                            from {0}.sys.index_columns ic
                                            inner join IndexColumnTempCTE cte on cte.index_id=ic.index_id and cte.index_column_id+1=ic.index_column_id and cte.object_id=ic.object_id
                                            inner join {0}.sys.columns c on ic.column_id=c.column_id and ic.object_id=c.object_id
                                        ),
                                        IndexColumnCTE as
                                        (
                                            select
                                            object_id,
                                            index_id,
                                            max(columnname) IndexColumns,
                                            max(includekey) IndexIncludeColumns
                                            from IndexColumnTempCTE
                                            group by object_id,index_id
                                        )
                                        select 
                                        IndexCTE.IndexName,
                                         IndexCTE.IndexType,
                                         IndexCTE.IsPrimaryKey,
                                         IndexCTE.IsUnique,
                                         IndexCTE.IsUniqueConstraint,
                                        IndexColumnCTE.IndexColumns,
                                        IndexColumnCTE.IndexIncludeColumns
                                        from IndexCTE
                                        inner join IndexColumnCTE on IndexCTE.object_id=IndexColumnCTE.object_id and IndexCTE.index_id=IndexColumnCTE.index_id
                                        order by IndexCTE.object_id", database);
            #endregion
            SqlParameter param = new SqlParameter("@tableName", SqlDbType.NVarChar, 100) { Value = database + ".dbo." + tableName };
            return DbHelperSQL.GetDataTable(connString, sql, param);
        }
    }

public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        private void btnConn_Click(object sender, EventArgs e)
        {
            try
            {
                string connString = this.txtConnString.Text;
                List<string> list = SqlQuery.GetDatabases(connString);
                this.treeView1.Nodes.Clear();
                this.treeView1.Nodes.AddRange(list.Select(item => new TreeNode { Text = item }).ToArray());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.GetBaseException().Message, "系统异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void treeView1_DoubleClick(object sender, EventArgs e)
        {
            try
            {
                string connString = this.txtConnString.Text;
                TreeNode node = this.treeView1.SelectedNode;

                switch (node.Level)
                {
                    case 0:
                        node.Nodes.Clear();
                        node.Nodes.AddRange(SqlQuery.GetTables(connString, node.Text).Select(kv => new TreeNode { Text = kv.Key, Tag = kv.Value }).ToArray());
                        node.Expand();
                        break;
                    case 1:
                        this.gridColumns.DataSource = SqlQuery.GetColumns(connString, node.Parent.Text, node.Tag.ToString());
                        this.gridIndexs.DataSource = SqlQuery.GetIndexs(connString, node.Parent.Text, node.Tag.ToString());
                        break;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.GetBaseException().Message, "系统异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }

 

附上源码下载：SqlQueryAnalyzer.rar

release目录下为已经编译好的程序SqlQueryAnalyzer.exe可以直接打开使用，src目录为源码