Get table structure in SQL (field name, type, length, description)

When writing code generator encountered such a problem, want to set up a database after the field description as comments written into the class file, so we searched the Internet a lot of code a long long way (of course, my code is very long), the pro-test a simple and easy to understand the statement, but also the most reproduced method:

SELECTTableName= object_name(c.object_id), Columnsname=C.name, Description=Ex.value, ColumnType=T.name, Length=C.max_length fromsys.columns C Left OUTER JOINsys.extended_properties ex onex.major_id=C.object_id  andex.minor_id=c.column_id andEx.name= 'ms_description'  Left outer Joinsystypes T onc.system_type_id=T.xtypeWHERE ObjectProperty(c.object_id,'ismsshipped')=0  and object_name(c.object_id)='Table name'

Pro-Test is available no problem, but there is a problem when testing

My table does have a title and category field, but why did you find two of the results?

See ColumnType not difficult to understand, these two one is my own table field, and the other is the System reservation field , then the problem is back to my original use of the statement:

Select  from where table_name=' table name '

The result of the query is this:

The result of shaping and ntext type is that I do not need the length of the type, the result is null is my intention, but there is no description of the field I want. So I'm going to try two statements together, and before that we need to know what we need in the first query statement.

So I tried to query the contents of the following 3 tables, in order to understand its structure. We see

Select *  from sys.extended_properties Select *  from Information_schema.columns Select *  from Sys.columns

The first is select * from Sys.extended_properties

The results are as follows:

So the description of all the tables is here.

And then we'll see.

SELECT * FROM Information_schema.columns

SELECT * FROM Sys.columns

The two tables contain a lot of content, here is not, you can check their own to see the content.

After comparison, we conclude that object_name (c.object_id) is the method to convert object_id from sys.columns to table name in the method we first searched for. But there is no information we need in sys.columns, so the object_name () method is reserved;

Next we see in the previous Sys.extended_properties table major_id This field, what does this field mean? So I got a look at the contents of sys.columns.object_id=sys.extended_properties.major_id from sys.columns, such as the above

Select *  from where object_id = 613577224

The result is exactly the field information in the same table, and concludes:

major_id and sys.columns in table sys.extended_properties are associated with object_id (primary key foreign key relationship?). ）

Finally, we found the fields in the sys.extended_properties table and the information_schema.columns table, that is:

SYS.EXTENDED_PROPERTIES.MAJOR_ID and Information_schema.columns.TABLE_NAME

Because sys.columns. object_id is sys.extended_properties.major_id so object_name ( sys.extended_properties.major_id) =Information_schema.columns.TABLE_NAME is the bridge between the two.

Say a bunch of crap, look at the final statement:

Select from as a left  join as on a. table_name=object_name and a.ordinal_position=where a. table_name=' table name '

Results:

This only requires a null field to be judged in the code!

Get table structure in SQL (field name, type, length, description)