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