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

SQL statement that queries all table field properties in a database 1. Get all user names: SELECT name from Sysusers where status= ' 2 ' and islogin= ' 1 ' islogin= ' 1 ' represent account islogin= ' 0 ' table The role status= ' 2 ' indicates that the user account status= ' 0 ' represents the System account 2. Get all database names: SELECT name from Master. sysdatabases ORDER by name 3. Gets 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 database All types SELECT Name from systypes 6. Get primary key field SEL ECT 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: (a) Several of these information outputs have been selected in order 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, the relationship is more complex, can be expressed roughly as follows: syscolumns the column information in the table and table Id,sysobjects table contains the primary key name (that is, pk_table similar) and table id,sysindexes stored in the With 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 after one item.

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 varchar var Char

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: Gets all the fields of a table select name from sys Columns where id=object_id (' Table name ') 3: Queries 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 tab le_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, do not copy data select top 0 * into [T1] from [T2] 10. Connect to remote database Sele CT * from OpenDataSource (' SQLOLEDB ', ' Data source= remote IP; User Id=sa; password= password '). The library name. dbo. Table name 11. Gets 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 ';

"Blog" http://virusswb.cnblogs.com/

"MSN"[email protected]

"description" Reproduced please indicate the source, thank you

Original address: http://blog.chinaunix.net/uid-21375345-id-2891305.html

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