Oracle|sql all table names in the Server|access database, field names

Oracle|sql server|access Database Tutorial All table names, field names

SELECT * FROM user_tables WHERE table_name = ' username '

Use the following statement if you are logged on with this user:
SELECT *
From User_tables;
If you are using another user:
SELECT * from All_tables WHERE owner= ' user_name '
First, the first sentence: is the query of the user under all the tables? Ordinary users, just granted Connect and resource permissions, you can also view some of the system tables?
How do I view all of the tables that the user created under "Log on as this user"?
Secondly, in the second sentence, when I log in as sys, how can I not use this sentence? SELECT *
From All_tables WHERE owner= ' xiaoming ' xiaoming is the user I created myself, and only granted Connect and resource permissions.

SQL SERVER
View all table names:
Select name from sysobjects where type= ' U '

Query table for all field names:
Select name from syscolumns Where id=object_id (' Table name ')

SELECT * FROM Information_schema.tables
SELECT * FROM Information_schema.views
SELECT * FROM Information_schema.columns

ACCESS

View all table names:
Select name from Msysobjects where type=1 and flags=0

The msysobjects is a system object, and the default is hidden. The tools, options, views, displays, and system objects can be displayed.

Reference: SQL Server get library name, table name

1. Get the basic field properties of a table

--Get table structure in SQL Server
SELECT syscolumns.name,systypes.name,syscolumns.isnullable,
Syscolumns.length
From syscolumns, systypes
WHERE Syscolumns.xusertype = Systypes.xusertype
and syscolumns.id = object_id (' Your table name ')

2. Get descriptive information for the field

--Get the table structure primary key in SQL Server, and describe
DECLARE @table_name as varchar (max)
Set @table_name = ' Your table name '
Select Sys.columns.name, Sys.types.name, Sys.columns.max_length, Sys.columns.is_nullable,
(SELECT COUNT (*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity,
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description
From Sys.columns, Sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and Sys.columns.system_type_ id=sys.types.system_type_id and Sys.tables.name= @table_name ORDER by sys.columns.column_id

3. Increment field of a separate query table

--Query table increment field individually
Select [Name] from syscolumns where
id=object_id (N ' Your table name ') and ColumnProperty (Id,name, ' isidentity ') =1

4. Get the primary foreign key of the table

--Get Table primary FOREIGN KEY constraint
EXEC sp_helpconstraint ' your table name ';

5. A fairly complete table structure query

--a very comprehensive table structure
EXEC sp_helpconstraint ' your table name ';

SELECT Table name = Case A.colorder when 1 THEN c.name ELSE "End,

Preface = A.colorder,

Field name = A.name,

id = case ColumnProperty (a.id,a.name, ' isidentity ') when 1 THEN ' √ ' ELSE ' "End,

Primary KEY = case

When EXISTS (SELECT * from sysobjects WHERE xtype= ' PK ')

and name in (SELECT [name] from sysindexes WHERE id=a.id

and Indid in (SELECT indid from Sysindexkeys WHERE id=a.id

and Colid in (SELECT colid from syscolumns WHERE id=a.id

and Name=a.name))) THEN ' √ ' ELSE ' end,

Type = B.name,

Number of bytes = A.length,

Length = ColumnProperty (a.id,a.name, ' Precision '),

decimal = Case ISNULL (columnproperty (a.id,a.name, ' Scale '), 0) when 0 THEN ' ELSE CAST (ColumnProperty (a.id,a.name, ' Scale ') as V Archar) End,

Allow NULL = case a.isnullable time 1 THEN ' √ ' ELSE ' end,

Default = ISNULL (D.[text], ""),

Description = ISNULL (E.[value], "")

From Syscolumns A

Left JOIN systypes B on A.xtype=b.xusertype

INNER JOIN sysobjects C on a.id=c.id and c.xtype= ' U ' and c.name<> ' dtproperties '

Left JOIN syscomments D on a.cdefault=d.id

Left JOIN sys.extended_properties E on A.id=e.class and a.colid=e.minor_id

Order by C.name, A.colorder

6. Get all the library names

--Get the name of the library that is encountered in the server

SELECT * FROM Master. sysdatabases

7. Get all the tables for all libraries on the server

--Get all table names for all libraries on the server

Use master

DECLARE @db_name varchar (100)

DECLARE @sql varchar (200)

DECLARE cur_tables cursor

For

Select name from sysdatabases/*where name like ' by_% ' *

Open Cur_tables

FETCH NEXT from Cur_tables into @db_name

While @ @fetch_status = 0

Begin

--set @db_name = @db_name + '. Dbo.sysobjects '

Print @db_name

Set @sql = ' select * from ' + @db_name + '. dbo.sysobjects where xtype = ' U '

EXEC (@sql)

FETCH NEXT from Cur_tables into @db_name

End

Close Cur_tables

Deallocate cur_tables

Go