Methods to get all libraries, tables, and field names in SQL Server database

Citation: http://blog.sina.com.cn/s/blog_7c0e26230100x2zl.html

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 ': represents all user tables;
Xtype= ' S ': denotes all system tables;

3. Get all field names:
SELECT Name from syscolumns WHERE id=object_id (' TableName ')

Access:

If a table exists, how do I get this table?
SELECT * from msysobjects where name= ' examresulttime ' and type=1 and flags=0

Get all the table names in the database
SELECT name from Msysobjects where type=1 and flags=0

2. 1. Get all user names:
4. Select name  from Sysusers where status=' 2 ' and islogin=' 1 '
6. islogin=' 1 ' : Indicates account
8. islogin=' 0 ' : Indicates a role
10. status=' 2 ' : Indicates user account
12. status=' 0 ' : Indicates a system account
16. 2. Get all database names:
18. Select Name  from Master.. sysdatabases OrDER by Name
22. 3. Get all table names:
24. Select Name  from DatabaseName. SysObjects Where xtype=' U ' orDER by Name
26. xtype=' U ' : represents all user tables;
28. xtype=' S ' : denotes all system tables;
32. 4. Get all field names:
34. Select Name  from syscolumns Where id=object_id (' table name ')
38. 5. Get all types of databases:
40. Select name from systypes
44. 6. Get the primary key field:
46. Select name  from syscolumns Where id=object_id (' table name ') and colid= (select top 1 keyno from Sysindexkeys where id=object_id (' table name '))
50. 7, get the basic information of table field:
52. Program code
54. Select
56. Field name =rtrim (b.name),
58. Primary key = case if h.id is isn't NULL then ' PK ' ELSE " END,
60.    field type =type_name (b.xusertype) +case   when  b.colstat&1=1  then    + convert ( varchar ,  ident_seed (A. name )) + +convert ( varchar , IDENT_INCR (A. name )) + ')] '   else   "   end ,    
62. Length =b.length,
64. Allow NULL = case b.isnullable if 0 then ' N ' ELSE ' Y ' END,
66. Default value =isnull(E.text, "),
68. Field Description =IsNull(C.value, ")
70. from sysobjects A, syscolumns b
72. Left OUTER JOIN sysproperties c on b.id = C.id and b.colid = C.smallid
74. Left OUTER JOIN syscomments e on b.cdefault = e.id
76. 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)   
78. Where (a.id=b.id) and  (a.id=object_id (' table to query ')) --The table you want to query is changed to the name of the table you want to query
80. OrDER by B.colid
84. Program code
86. Select
88. Table name = case when A.colorder=1 and then D.name else ' end,
90. Table Description = case if a.colorder=1 then isnull(F.value,") Else " end,
92. Field Ordinal =a.colorder,
94. Field name =a.name,
96. Identification = case when ColumnProperty (a.id,a.name,' isidentity ') =1 then ' √ ' Else ' end,
97. Primary KEY = Case whenExistsSelect1 fromsysobjectswhereXtype=' PK ' andnameinch(Selectname fromsysindexesWhereIndidinch(SelectIndid fromSysindexkeysWhereId=a.id andColid=a.colid))) Then' √ 'Else"'End,
99. Type =b.name,
101. Field Length =a.length,
103. Takes up bytes =columnproperty (a.id,a.name,' PRECISION '),
105. Number of decimal digits =IsNull(ColumnProperty (a.id,a.name,' scale '), 0),
107. Allow null = case Time a.isnullable=1 then ' √ 'else ' end,
109. Default value =isnull(E.text,"),
111. Field Description =isnull(G.[value],")
113. from Syscolumns a
115. Left Join systypes b on A.xusertype=b.xusertype
117. Inner join sysobjects D on (a.id=d.id)and (d.xtype=' U ')and (d.name <>' dtproperties ')
119. Left Join syscomments e on a.cdefault=e.id
121. Left join sysproperties G on (a.id=g.id)and (A.colid=g.smallid)
123. Left join sysproperties F on (d.id=f.id)and (f.smallid=0)
125. --where d.name= ' table to query '--if you only query the specified table, add this condition
127. Order by A.id,a.colorder

Methods to get all libraries, tables, and field names in SQL Server database