Obtains the basic information of a specified table in the specified database of sqlserver.

* ** Filename: GetTableInfor. SQL * Description: used to obtain the field, length, Description, and other data information of the specified table in the specified database, so that the code generator can be developed later. Tbname can be changed to your own

/* ===================================================== ========================================================== ** Filename: getTableInfor. SQL * Description: obtains the fields, field lengths, field descriptions, and other related data of the specified table in the specified database, so that the code generator can be prepared later. Tbname can be changed to your own

/* ===================================================== ==========================================================
*
* Filename: GetTableInfor. SQL
* Description: obtains the fields, length, Description, and other related data of the specified table in the specified database.
Prepare the development code generator. You can change tbname to the name of the table you want to query.
* Version: 1.0
* Created: 2012.08.02
* Author: liangjw
* E-mail: liangjw0504@163.com
* Q: 592568532
* Profile Url: http://90ideas.net/
* Company: Copyright (C) Create Family Wealth Power By Peter
*
========================================================== ================================================= */
* Remarks: uploads the encapsulation of some of the frequently-used methods summarized by yourself. There are deficiencies and imperfections. I hope you can point them out and wish to work together.
* This article mainly studies the development of erp, cms, crm, B2B, oa and other systems and websites. We welcome IT personnel who share the same pursuit and learning to learn and communicate with each other.
* Learn and discuss about asp.net mvc, Ajax, jquery, html/css, xml, sqlserver, wpf, IIS, server construction, and security technologies. <无>

/* ===================================================== ========================================================== ** Filename: getTableInfor. SQL * Description: obtains the fields, field lengths, field descriptions, and other related data of the specified table in the specified database, so that the code generator can be prepared later. Tbname can be changed to the name of the table you want to query. * Version: 1.0 * Created: 2012.08.02 * Author: Your name * Company: Copyright (C) create Family Wealth Power By Peter * ==================================== ========================================================== ===== */SELECT colorder = C. column_id, ColumnName = C. name, TypeName = T. name, Length = case when t. name = 'nchar 'then c. max_length/2 when t. name = 'nvarchar 'then c. max_length/2 else c. max_length END, Preci = C. precision, Scale = C. scale, IsIdentity = case when c. is_identity = 1 THEN n'1' ELSE n'' END, isPK = ISNULL (IDX. primaryKey, n''), Computed = case when c. is_computed = 1 THEN n'1' ELSE n'' END, IndexName = ISNULL (IDX. indexName, n''), IndexSort = ISNULL (IDX. sort, n''), Create_Date = O. create_Date, Modify_Date = O. modify_date, cisNull = case when c. is_nullable = 1 THEN n'1' ELSE n'' END, defaultVal = ISNULL (D. definition, n''), deText = ISNULL (PFD. [value], n'') FROM sys. columns c inner join sys. objects o on c. [object_id] = O. [object_id] AND (O. type = 'U' or o. type = 'V') and o. is_ms_shipped = 0 inner join sys. types t on c. user_type_id = T. user_type_id left join sys. default_constraints d on c. [object_id] = D. parent_object_id and c. column_id = D. parent_column_id and c. default_object_id = D. [object_id] left join sys. extended_properties pfd on pfd. class = 1 and c. [object_id] = PFD. major_id and c. column_id = PFD. minor_id left join sys. extended_properties ptb on ptb. class = 1 and ptb. minor_id = 0 and c. [object_id] = PTB. major_id left join -- index and primary key information (select idxc. [object_id], IDXC. column_id, Sort = CASE INDEXKEY_PROPERTY (IDXC. [object_id], IDXC. index_id, IDXC. index_column_id, 'isscending') WHEN 1 THEN 'desc' WHEN 0 THEN 'asc 'else' 'end, PrimaryKey = case when idx. is_primary_key = 1 THEN n'1' ELSE n'' END, IndexName = IDX. name FROM sys. indexes idx inner join sys. index_columns idxc on idx. [object_id] = IDXC. [object_id] and idx. index_id = IDXC. index_id left join sys. key_constraints kc on idx. [object_id] = KC. [parent_object_id] and idx. index_id = KC. unique_index_id inner join -- only 1st index information (SELECT [object_id], Column_id, index_id = MIN (index_id) FROM sys. index_columns group by [object_id], Column_id) idxcuq on idxc. [object_id] = IDXCUQ. [object_id] and idxc. column_id = IDXCUQ. column_id and idxc. index_id = IDXCUQ. index_id) idx on c. [object_id] = IDX. [object_id] and c. column_id = IDX. column_idWHERE O. name = N 'tbname' -- the database table name tbname can be replaced with the order by o table to be queried. name, C. column_id