1. Get all database names:
Select name from Master .. sysdatabases order by name
2. Get all table names:
Select name from databasename .. sysobjects where xtype = 'U' order by name
Xtype = 'U': indicates all user tables;
Xtype = 's': indicates all system tables;
3. Get all field names:
Select name from syscolumns where id = object_id ('tablename ')
Access:
If the table exists, how can we get the table?
Select * From msysobjects where name = 'examresulttime' and type = 1 and flags = 0
// Obtain the names of all tables in the database.
Select name from msysobjects where type = 1 and flags = 0
1. Retrieve all user names:
Select name from sysusers where status = '2' and islogin = '1'
Islogin = '1': indicates the account
Islogin = '0': indicates the role
Status = '2': indicates the user account
Status = '0': indicates a unified account.
2. Obtain all database names:
Select name from Master .. sysdatabases order by name
3. Get all table names:
Select name from databasename .. sysobjects where xtype = 'U' order by name
Xtype = 'U': indicates all user tables;
Xtype = 's': indicates all system tables;
4. Obtain the names of all fields:
Select name from syscolumns where id = object_id ('table name '')
5. Obtain all database types:
Select name from policypes
6. Obtain the primary key field:
Select name from syscolumns where id = object_id ('table name') and colid = (select top 1 keyno from sysindexkeys where id = object_id ('table name '))
7. obtain basic information about table fields:
Program code
Select
Field name = rtrim (B. Name ),
Primary Key = case when H. ID is not null then 'pk 'else' 'end,
Field Type = type_name (B. xusertype) + case when B. colstat & 1 = 1 then' [ID ('+ convert (varchar, ident_seed (. name) + ',' + convert (varchar, ident_incr (. name) + ')] 'else' end,
Length = B. length,
Allow null = case B. isnullable when 0 then 'n' 'else' end,
Default Value = isnull (E. Text ,''),
Field description = isnull (C. Value ,'')
From sysobjects A, syscolumns B
Left Outer Join sysproperties C on B. ID = C. ID and B. colid = C. smallid
Left Outer Join syscomments e on B. cdefault = E. ID
Left Outer Join (select G. ID, G. colid from sysindexes F, sysindexkeys g where (F. id = G. ID) and (F. indid = G. indid) and (F. indid> 0) and (F. indid <255) and (F. status & 2048) <> 0) h on (B. id = H. ID) and (B. colid = H. colid)
Where (A. ID = B. ID) and (A. ID = object_id ('table to query') -- change the name of the table to be queried.
Order by B. colid
Program code
Select
Table name = case when a. colorder = 1 then D. Name else ''end,
Table description = case when a. colorder = 1 then isnull (F. Value, '') else'' end,
FIELD No. = A. colorder,
Field name = A. Name,
Id = case when columnproperty (A. ID, A. Name, 'isidentity ') = 1 then' √ 'else' end,
Primary Key = case when exists (select 1 from sysobjects where xtype = 'pk' and name in (Select name from sysindexes where indid in (select indid from sysindexkeys where id =. ID and colid =. colid) then' √ 'else' end,
Type = B. Name,
Field Length = A. length,
Bytes occupied = columnproperty (A. ID, A. Name, 'precision '),
Decimal places = isnull (columnproperty (A. ID, A. Name, 'Scale'), 0 ),
Allow null = case when a. isnullable = 1 then '√ 'else' 'end,
Default Value = isnull (E. Text ,''),
Field description = isnull (G. [value], '')
From syscolumns
Left join policypes B on A. xusertype = B. xusertype
Inner join sysobjects D on (A. ID = D. id) and (D. xtype = 'U') and (D. Name <> 'dtproperties ')
Left join syscomments e on A. cdefault = E. ID
Left join sysproperties g on (A. ID = G. ID) and (A. colid = G. smallid)
Left join sysproperties F on (D. id = f. ID) and (F. smallid = 0)
-- Where D. Name = 'table to be query' -- this condition is added if only the specified table is queried.
Order by A. ID, A. colorder