SQL gets all database names, table names, stored procedures, and parameter lists

Source: Internet
Author: User
Tags null null one table

1. Get all user names: SELECT name from Sysusers where status= ' 2 ' and islogin= ' 1 '
islogin= ' 1 ' means account
Islogin= ' 0 ' indicates a role
Status= ' 2 ' represents a user account
Status= ' 0 ' indicates a system account
2. Get all database names:
SELECT Name from Master. sysdatabases ORDER by Name
3. Get all table names
SELECT Name from DatabaseName. SysObjects Where xtype= ' U ' ORDER by Name
Xtype= ' U ': represents all user tables;
Xtype= ' S ': denotes all system tables;
4. Get all field names:
SELECT Name from syscolumns WHERE id=object_id (' TableName ')
5. Get all types of databases
Select name from systypes
6. Get the primary key field
Select name from syscolumns where id=object_id (' table name ') and colid= (select top 1 keyno from Sysindexkeys where id=object_id (' Table name '))


[Comprehensive network data collation]

1. Get all database names:
(1), Select Name from Master. sysdatabases ORDER BY Name
2. Get all table names:
(1), Select Name from SysObjects Where xtype= ' U ' OrDER by Name
Xtype= ' U ': represents all user tables;
Xtype= ' S ': denotes all system tables;

(2), SELECT name from sysobjects WHERE type = ' U ' and sysstat = ' 83 '

Note: The general situation requires only type = ' U ', but sometimes the system tables are mixed (for unknown reasons), and the system tables can be deleted after the following sentence.


3. get all field names:
(1), Select Name from syscolumns Where id=object_id (' TableName ')

(2), SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length from syscolumns, systypes WHERE Syscolumns.xusertype = Systypes.xusertype and "syscolumns.id = object_id (' TableName ')

Note the point:
(a) Several of these information outputs have been selected to highlight some of the important points.
(b) The syscolumns table contains only the data type number, to obtain the complete name needs to be looked up from the systypes table, the general user uses the data type to use the Xusertype correspondence is better, does not appear one to many the situation.
(c) Syscolumns.length gets the length of the physical memory, so the types of nvarchar and varchar are displayed in the database in half.

4 , get the column names contained in the primary key in the table:

SELECT syscolumns.name from syscolumns,sysobjects,sysindexes,sysindexkeys WHERE syscolumns.id = object_id (' t Ablename ') and Sysobjects.xtype = ' PK ' and sysobjects.parent_obj = syscolumns.id and sysindexes.id = syscolumns.id and sys Objects.name = sysindexes.name and sysindexkeys.id = syscolumns.id and Sysindexkeys.indid = Sysindexes.indid and Syscolumn S.colid = Sysindexkeys.colid

Note: This is found in 4 system tables, and the relationship is more complex and can be expressed roughly as:
SYSCOLUMNS contains the column information in the table and the table id,sysobjects the primary key name (that is, pk_table similar) and the table id,sysindexes contains the primary key name and table ID and index number, The table ID and index number and column number are stored in the Sysindexkeys, and a column name is found when one item is matched.

Additional SQL code

Select Syscolumns.name, Systypes.name, syscolumns.length from syscolumns

Left join systypes on syscolumns. Xusertype =systypes. Xusertype

where id= (select id from sysobjects where name= ' order main file ');

Go

Or, in the same way, execute the result:

Select Syscolumns.name, Systypes.name, syscolumns.length from Syscolumns,systypes

Where (syscolumns.id=object_id (' Order main file ') and Syscolumns.xusertype=systypes.xusertype)

Order BY Syscolumns.colorder;

Go

Execution Result: (field appears only once, normal)

Order Number int 4

Customer Ref. NVARCHAR 10

Employee Number int 4

Order Date DateTime 8

Order Date DateTime 8

Delivery date DateTime 8

Shipping Method int 4

Freight Money 8

Consignee nvarchar 80

Shipping Address nvarchar 120

Delivery City nvarchar 30

Delivery Borough nvarchar 30

Delivery postal Code nvarchar 20

Delivery Country Region nvarchar 30

Select Syscolumns.name, Systypes.name, syscolumns.length from syscolumns

Left join systypes on syscolumns.xtype=systypes.xtype

where id= (select id from sysobjects where name= ' order main file ');

Go

Execution Result: (some fields appear two times, data types are different)

Order Number int 4

Customer Ref. NVARCHAR 10

Customer Ref. sysname 10

Employee Number int 4

Order Date DateTime 8

Order date date of birth type 8

Order Date DateTime 8

Date of shipment Birth date type 8

Delivery date DateTime 8

Delivery date date of birth type 8

Shipping Method int 4

Freight Money 8

Freight Salary Type 8

Consignee nvarchar 80

Consignee sysname 80

Shipping Address nvarchar 120

Shipping Address sysname 120

Delivery City nvarchar 30

Delivery City sysname 30

Delivery Borough nvarchar 30

Delivery Borough sysname 30

Delivery postal Code nvarchar 20

Delivery postal Code sysname 20

Delivery Country Region nvarchar 30

Delivery Country Region sysname 30

Querying stored Procedures Departmentsalaryinfo all information, information contained in the system view syscolumns , systypes in

Select Syscolumns.*, systypes.* from syscolumns

Left join systypes on Syscolumns.xusertype=systypes.xusertype

where id= (select id from sysobjects where name= ' departmentsalaryinfo ');

Go

exec sp_procedure_params_rowset @procedure_name = ' departmentsalaryinfo ';

Go

Execution Result:

North Wind trade dbo departmentsalaryinfo;1 @RETURN_VALUE 0 4 0 NULL 0 3 NULL null-null NULL int int

North Wind TRADE dbo departmentsalaryinfo;1 @department 1 1 0 NULL 1 129 null NULL NULL varchar VARC Har

North Wind trade dbo departmentsalaryinfo;1 @average 2 2 0 NULL 1 6 NULL null-NULL NULL Money Money

North Wind trade dbo departmentsalaryinfo;1 @maximum 3 2 0 NULL 1 6 NULL null-NULL NULL Money Money

North Wind trade dbo departmentsalaryinfo;1 @minimum 4 2 0 NULL 1 6 NULL null-NULL NULL Money Money

--Parameter name, argument type, parameter length in stored procedure

Select Syscolumns.name, Systypes.name, syscolumns.length from syscolumns

Left join systypes on Syscolumns.xusertype=systypes.xusertype

where id= (select id from sysobjects where name= ' departmentsalaryinfo ');



1: Get all user tables in the current database
Select Name from sysobjects where xtype= ' u ' and status>=0
2: Get all fields of a table
Select name from syscolumns where id=object_id (' Table name ')
3: Querying all databases created by the user
SELECT * FROM Master. sysdatabases D where Sid not in (select Sid from
Master.. syslogins where name= ' sa ')
Or
Select dbid, name as Db_name from master. sysdatabases where Sid <> 0x01
4: Querying a table for fields and data types
Select Column_name,data_type from Information_schema.columns
WHERE table_name = ' table name '
[n]. [Title]:
Select * from TableName Order by CustomerName
[n]. [Title]:
8. How to modify the name of the database:
Sp_renamedb ' Old_name ', ' new_name '
9. Copy only one table structure and not copy data
Select top 0 * into [T1] from [T2]
10. Connecting to a remote database
SELECT * from OpenDataSource (' SQLOLEDB ', ' Data source= remote IP; User
Id=sa; password= password '). Library name. dbo. Table name 11. Get all tables in the current Oracle database
Select table_name from USER_TABLES12. Gets the type of all fields in the current Oracle table Select
Column_name,data_type,data_length,nullablefrom
User_tab_cols where table_name= ' teacher ';

SQL gets all database names, table names, stored procedures, and parameter lists

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.