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