Oracle | sqlserver | names of all tables in the access database, Field Names

Oracle | sqlserver | names of all tables in access. Field Names * fromuser_tableswheretable_name: SELECT * FROMUSER_TABLES; other users: SELECT * FROMALL_TABLESWHEREOWNERUSER_NAME & nbsp; Oracle | SQL server | names of all tables in access, Field Names

* From user_tables where table_name = 'username'

Use the following statement to log 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: Are all tables under this user queried? A common user only grants the connect and resource permissions. Can I view some system tables?
How can I view all tables created by myself under user logon?
Second, when I Log On As sys, how can I not use this sentence? SELECT *
FROM ALL_TABLES where owner = 'xiaoming' [xiaoming is a user created by myself. It only grants connect and resource permissions]

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

Query the names of all fields in the table:
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

MSysObjects is a system object, which is hidden by default. You can use tools, options, views, displays, and system objects to display them.

Reference: SQL server obtains the Database Name and table name.

1. Get basic field attributes of the table

-- Obtain the table structure in SqlServer
SELECT syscolumns. name, policypes. name, syscolumns. isnullable,
Syscolumns. length
FROM syscolumns, policypes
WHERE syscolumns. xusertype = policypes. xusertype
AND syscolumns. id = object_id ('your table name ')

2. Obtain the field description.

-- Obtain the primary key and description of the table structure in SqlServer.
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 order by sys. columns. column_id

3. Independently query incremental fields of the table

-- Separately query the incremental field of the table
Select [name] from syscolumns where
Id = object_id (n' your table name') and COLUMNPROPERTY (id, name, 'isidentity ') = 1

4. Obtain the table's primary and Foreign keys

-- Obtain the table's primary and foreign key constraints
Exec sp_helpconstraint 'your table name ';

5. Complete table structure query

-- Very comprehensive table structure
Exec sp_helpconstraint 'your table name ';

SELECT Table name = CASE a. colorder WHEN 1 THEN c. name ELSE ''end,

Order = 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,

Bytes = a. length,

Length = COLUMNPROPERTY (a. id, a. name, 'precision '),

Decimal = case isnull (COLUMNPROPERTY (. id,. name, 'Scale'), 0) WHEN 0 THEN ''else cast (COLUMNPROPERTY (. id,. name, 'Scale') as varchar) END,

Allow null = CASE a. isnullable WHEN 1 THEN '√ 'else'' END,

Default Value = ISNULL (d. [text], ''),

Description = ISNULL (e. [value], '')

FROM syscolumns

Left join policypes 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 database names

-- Get the name of the database on the server

Select * from master .. sysdatabases

7. Retrieve all tables of all databases on the server

-- Obtain the names of all tables in all databases 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