Get all the column information for all the tables in all of the SQL Server libraries

Recently remembered to make a project code generator that directly generates the underlying code:

This is unavoidable to read the information that the database already has.

Don't say much nonsense. Open the whole.

SELECT  from MASTER.. sysdatabases  -- Read all libraries

The top statement only gets a single list. The name of the database is all inside.

Then select database after: Relink the database by this database connection ... Execute the following statement

SELECT  from WHERE TYPE='U'  -- Read all tables

The top statement is still the resulting single column, and the stored value is not a table in the currently selected database

User selection: Get the table name:

SELECTSc. Name--query "column name" "Type " "Length" "description",[TYPE] =St. Name+  Casest.user_type_id when  A  Then '('+CAST(Sc.scale as VARCHAR)+')'                                       -- Time     when  the  Then '('+CAST(Sc.scale as VARCHAR)+')'                                       --datetime2     when  +  Then '('+CAST(Sc.scale as VARCHAR)+')'                                       --DateTimeOffset     when 106  Then '('+CAST(SC.Precision   as VARCHAR)+','+ CAST(Sc.scale as VARCHAR)+')'  --decimal     when 108  Then '('+CAST(SC.Precision   as VARCHAR)+','+ CAST(Sc.scale as VARCHAR)+')'  --Numeric     when 165  Then '('+ISNULL(CAST(Nullif(Sc.max_length,-1) as VARCHAR),'Max')+')'        --varbinary     when 167  Then '('+ISNULL(CAST(Nullif(Sc.max_length,-1) as VARCHAR),'Max')+')'        --varchar     when 173  Then '('+ISNULL(CAST(Nullif(Sc.max_length,-1) as VARCHAR),'Max')+')'        --binary     when 175  Then '('+ISNULL(CAST(Nullif(Sc.max_length,-1) as VARCHAR),'Max')+')'        --Char     when 231  Then            CaseSc.max_length when -1  Then '(max)'          ELSE '('+ISNULL(CAST(Nullif(Sc.max_length/ 2,-1) as VARCHAR),'Max')+')'      --nvarchar        END        when 239  Then '('+ISNULL(CAST(Nullif(Sc.max_length,-1) as VARCHAR),'Max')+')'     --nchar    ELSE "'       END            , Case  whenSc. Is_nullable= 0  Then 'Not NULL'                                  --whether to allow empty         whenSc. Is_nullable= 1  Then 'NULL'           ELSE 'UNKNOWN' ENDisnullable, (SELECTSYS. Extended_properties. VALUE fromsyscolumnsINNER JOINSYS. Extended_properties onSyscolumns.id=SYS. Extended_properties. major_id andsyscolumns. COLID=SYS. Extended_properties. minor_idINNER JOINSYSOBJECTS onSyscolumns.id=sysobjects.idWHERESysobjects.name=So.name andSyscolumns.name=Sc.name) Description--So . Name Table name, SC. Name Column name fromSYS. COLUMNS Sc,sys. TYPES St,sys. SYSOBJECTS so--Column name information and table structure informationWHEREsc.user_type_id=st.user_type_id andSc.object_id =So.id andSo. XTYPE='U'  andSo.name='Write table name'  

The statement above is copied from the Internet. I thanked the great God first.

In the statement, simply write the table name at the end: You can get a table of four columns. Field name/field type/empty/memo, respectively

These four things are basically the most important four elements of generating code. Indispensable..

After that, the loop is good. Loop-generated Read template: Bind data:

Get all the column information for all the tables in all of the SQL Server libraries