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

Source: Internet
Author: User
Tags access database

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

Related Article

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.