SQL statement Query table structure

Just doing a small project, a table in the database has more than 20 fields, using one I used to do. NET Entity class Generator One of the inputs or the idle trouble, so I intend to find a time to reinvent the one. NET entity class, the ability to automatically generate entity classes through the selected database and user table, and the entity class has comments, that is, the field Description field, and then add a function to generate the database document, in fact, the field Word, type, field description output a beautiful table just, As long as can get out to generate HTML table is easier, the Internet to find a bit, get the following useful SQL statement:

--querying non-system databasesSelectName fromMaster.. sysdatabaseswheredbid>4--Select all tables under the water database Use [Water] SELECTName fromsysobjectsWHEREXtype= 'U' OrXtype= 'S'--Select all user tables under the water database Use [Water] SELECTName fromsysobjectsWHEREXtype= 'U'  and ObjectProperty(ID,'ismsshipped')= 0--Query the admin table under the Water database for field name, length, type, field description Use [Water] SELECTA.[name]  as 'Field name', A.length'length'C.[name] 'type', E.value as 'Field Description'  fromsyscolumns a Left   JoinSystypes b onA.xusertype=B.xusertype Left     JoinSystypes C onA.xtype=C.xusertypeInner   Joinsysobjects D ona.ID=D.id andD.xtype='U'  Left JoinSys.extended_properties E ona.ID=e.major_id andA.colid=e.minor_id andE.name='ms_description'whereD.name='Admin' 

　　

The sys.extended_properties system built-in view is used to store the field descriptions, and only above the MSSQL2005 has this table, and the preceding sys. can not be removed, SQL2000 words the field description is present in another table, the name of the list is forgotten, Anyway, I don't need or bother to check ...
We hereby record!!!

SQL statement Query table structure