SQL query database table name, table column name, data type, primary key

1. Get all database names:
2.Select Name from Master: sysdatabases ORDER BY Name
3.
4.2. Get all table names:
5. (1)
6.Select name from SysObjects Where xtype= ' U ' OrDER by Name
7. Xtype= ' U ': represents all user tables;
8. Xtype= ' S ': denotes all system tables;
9. (2)
10.SELECT name from sysobjects WHERE type = ' U ' and sysstat = ' 83 '
11. Note: The general situation requires only type = ' U ', but sometimes the system tables are mixed (not known for any reason), and the system tables can be deleted after the following sentence.
12.
13.3. Get all field names:
14. (1),
15.Select Name from syscolumns Where id=object_id (' TableName ')
16. (2)
17.SELECT Syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length
18.FROM syscolumns, systypes
19.WHERE Syscolumns.xusertype = Systypes.xusertype and "syscolumns.id = object_id (' TableName ')
20. Note the point:
(a) Several of these information outputs have been selected in order to highlight some of the important elements.
(b) The syscolumns table contains only the data type number, to obtain the complete name needs to be looked up from the systypes table, the general user uses the data type to use the Xusertype correspondence is better, does not appear one to many the situation.
(c) Syscolumns.length gets the length of the physical memory, so the types of nvarchar and varchar are displayed in the database in half.
24.
25.4. Get the column names contained in the primary key in the table:
26.SELECT Syscolumns.name
27.FROM Syscolumns,sysobjects,sysindexes,sysindexkeys
28.WHERE syscolumns.id = object_id (' tablename ') and Sysobjects.xtype = ' PK ' and sysobjects.parent_obj = Syscolumns.id and Sysindexes.id = syscolumns.id and Sysobjects.name = sysindexes.name and sysindexkeys.id = Syscolumns.id and sysindexkeys.i Ndid = Sysindexes.indid and syscolumns.colid = Sysindexkeys.colid
29. Note: This is found in the 4 system tables, the relationship is more complex, can be expressed broadly as:
30.syscolumns contains the column information in the table and the table id,sysobjects the primary key name (that is, pk_table similar) and table id,sysindexes contains the primary key name and table ID and index number, The table ID and index number and column number are stored in the Sysindexkeys, and a column name is found when one item is matched.

SQL query database table name, table column name, data type, primary key