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)

恩，功能就這麼簡單，利用好了能在工作中省下不少事情，貼一下主要代碼

SqlQuery.cs

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);        }    }

Form1.cs

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目錄為源碼