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

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.