SQL Server table structure and index queryer

Because we often need to process a large number of tables, it is basically normal to view the table structure, data volume, and indexes, but the database in the production environment does not allow us to access directly, therefore, if you want to know the table structure and index information of the database in the production environment, you need to contact DBA frequently. To view the data volume of a specific table, use select count (1) the efficiency of the from table method is really slow and intolerable. As we call this method, we are asking people to do better than ourselves. It took us some time to make a small tool to view the database table structure and indexes. By the way, we learned about the system tables, you can use it with your favorite friends.

Tool functions are simple but practical (especially when developers are not allowed to use enterprise management to connect to the database in the production environment), which can improve efficiency.
1. List all databases and tables on the server based on the link string.
2. view the number of records, fields, and indexes (including covering indexes) of the table.

The default Interface is as follows:

Modify the data connection string as needed and click the link to automatically query the database information.

Double-click the database to load all the tables and read the number of records (the number in the brackets behind the table name indicates the number of records)

Double-click a table name to load the table structure and index information.

Here is a simple translation of list fields to help children's shoes with poor English skills.

Table Structure

Columnname	Columntype	Bytelength	Charlength	Scale	Isidentity	Isnullable	Remark
Field name	Field Type	Byte Length	Character Length	Decimal places	Auto-increment Column	Allow blank?	Field description

Table Index

Indexname	Indextype	Isprimarykey	Isunique	Isuniqueconstraint	Indexcolumns	Indexincludecolumns
Index name	Index type	Primary Key?	Unique or not	Unique constraint?	Indexed Columns	Overwrite the columns contained in the index

 

The remark field description in the table structure is the description of the column we add to the column, bytelength is the byte length of the field, charlength is the character length, these two are mainly for nchar, for nvarchar databases, for example, defining name nvarchar (50), the length of the displayed byte is 100, and the length of the character is 50 (nvarchar occupies two bytes). If you do not pay attention to it, it will be miserable, I was killed by this trick. (I like to use Alt + F1 to view table structure information. In this case, the nvarchar (50) field type will display a length of 100)

In the table index, indexcolumns indicates the columns included in the index,Indexincludecolumns indicates overwriting the columns contained in the index. For example, the index ix_test1, indexcolumns is name, and email, Indexincludecolumns is address, postcode, And the covered index can only be created on a non-clustered index, mainly to solve the bookmarked search (RID, key search ), the clustered index does not contain bookmarks, so you cannot create a overwriting index.

 
Create IndexIx_test1OnUsers (name, email) include (address, postcode)
 

Well, the function is so simple. If you use it, you can save a lot of work.Code

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 = 'text' 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,  "  System exception  "  , Messageboxbuttons. OK, messageboxicon. Error );}}  Private   Void Treeviewappsdoubleclick ( 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,  "  System exception  "  , Messageboxbuttons. OK, messageboxicon. Error );}}} 

 

Download the source code: sqlqueryanalyzer.rar

The release directory is compiled.ProgramSqlqueryanalyzer.exe can be opened and used directly. The src directory is the source code.