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

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

1. Get all user names: Select name from sysusers where status = '2' and islogin = '1' Islogin = '1' indicates the account Islogin = '0' indicates the role Status = '2' indicates the user account Status = '0' indicates a unified 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': indicates all user tables; Xtype = 's': indicates all system tables; 4. Get all field names: Select name from syscolumns where id = object_id ('tablename ') 5. Retrieve all database types Select name from policypes 6. Obtain 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 sorting] 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': indicates all user tables; Xtype = 's': indicates all system tables; (2) Select name from sysobjects where type = 'U' and sysstat = '83' Note: Generally, only type = 'U' is required, but sometimes the system tables are mixed in (I don't know why). After adding the following sentence, these system tables can be deleted. 3. Get all field names: (1) Select name from syscolumns where id = object_id ('tablename ') (2) Select syscolumns. name, policypes. name, syscolumns. isnullable, syscolumns. length from syscolumns, policypes where syscolumns. xusertype = policypes. xusertype and "syscolumns. id = object_id ('tablename ') Note: (A) In order to highlight some important content, several pieces of information are selected for output. (B) The syscolumns table only contains data type numbers. To obtain the complete name, you need to find it from the categorypes table. Generally, it is better to use xusertype for user data types, there will be no one-to-many cases. (C) syscolumns. length is the length of the physical memory, so nvarchar, varchar, and Other types are shown in the database as half of this. 4. Obtain the column names of the primary keys in the table: Select syscolumns. name from syscolumns, sysobjects, sysindexes, sysindexkeys where syscolumns. id = object_id ('tablename') and sysobjects. xtype = 'pk' and sysobjects. parent_obj = syscolumns. ID and sysindexes. id = syscolumns. ID and sysobjects. name = sysindexes. name and sysindexkeys. id = syscolumns. ID and sysindexkeys. indid = sysindexes. indid and syscolumns. colid = sysindexkeys. colid Note: This is found in four system tables. The relationship is complex and can be roughly expressed: Syscolumns contains the column information and table ID in the table. The sysobjects table contains the primary key name (similar to pk_table) and Table ID. sysindexes contains the primary key name, table ID, and index number, sysindexkeys contains the table ID, index number, and column number. After one item is matched, the column name can be found. Other SQL statementsCode Select syscolumns. Name, policypes. Name, syscolumns. length from syscolumns Left join policypes on syscolumns. xusertype = policypes. xusertype Where id = (select ID from sysobjects where name = 'order '); Go; Alternatively, the execution results are the same: Select syscolumns. Name, policypes. Name, syscolumns. length from syscolumns, policypes Where (syscolumns. ID = object_id ('order main file') and syscolumns. xusertype = policypes. xusertype) Order by syscolumns. colorder; Go Execution result: (the field appears only once and is normal) Order Number int 4 Customer No. nvarchar 10 Employee No. Int 4 Order Date datetime 8 Required Date: datetime 8 Delivery Date datetime 8 Delivery Method int 4 Freight money 8 Consignee nvarchar 80 Delivery Address nvarchar 120 Delivery city nvarchar 30 Delivery region nvarchar 30 Delivery zip code nvarchar 20 Delivery country/region nvarchar 30 Select syscolumns. Name, policypes. Name, syscolumns. length from syscolumns Left join policypes on syscolumns. xtype = policypes. xtype Where id = (select ID from sysobjects where name = 'order '); Go; Execution result: (some fields appear twice, with different data types) Order Number int 4 Customer No. nvarchar 10 Customer No. sysname 10 Employee No. Int 4 Order Date datetime 8 Order Date birthdate type 8 Required Date: datetime 8 Important date birthdate type 8 Delivery Date datetime 8 Delivery Date birthdate type 8 Delivery Method int 4 Freight money 8 Freight salary type 8 Consignee nvarchar 80 Sysname 80 Delivery Address nvarchar 120 Shipping address: sysname 120 Delivery city nvarchar 30 Shipping city sysname 30 Delivery region nvarchar 30 Shipping Administrative Region sysname 30 Delivery zip code nvarchar 20 Shipping postal code sysname 20 Delivery country/region nvarchar 30 Shipping country/region sysname 30 Query all the information of departmentsalaryinfo In the stored procedure. The information is contained in syscolumns and policypes in the system view. Select syscolumns. *, policypes. * From syscolumns Left join policypes on syscolumns. xusertype = policypes. xusertype Where id = (select ID from sysobjects where name = 'departmentsalaryinfo '); Go Exec sp_procedure_params_rowset @ procedure_name = 'destentsalaryinfo '; Go Execution result: Beifeng trade DBO departmentsalaryinfo; 1 @ return_value 0 4 0 null 0 3 Null null 10 null int North Wind trade DBO departmentsalaryinfo; 1 @ department 1 1 0 null 1 129 10 null varchar Beifeng trade DBO departmentsalaryinfo; 1 @ average 2 2 0 null 1 6 null 19 null money Money North Wind trade DBO departmentsalaryinfo; 1 @ maximum 3 2 0 null 1 6 null 19 null money Money Beifeng trade DBO departmentsalaryinfo; 1 @ minimum 4 2 0 null 1 6 null 19 null money Money -- Parameter Name, parameter type, and parameter length in the Stored Procedure Select syscolumns. Name, policypes. Name, syscolumns. length from syscolumns Left join policypes on syscolumns. xusertype = policypes. xusertype Where id = (select ID from sysobjects where name = 'departmentsalaryinfo ');   1: obtain 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: Query 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: query the fields and Data Types of a table 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 Database Name: Sp_renamedb 'old _ name', 'new _ name' 9. copy only one table structure without copying data Select top 0 * into [T1] from [T2] 10. connect to a remote database Select * From OpenDataSource ('sqlodb', 'Data source = remote IP address; User Id = sa; Password = password'). Database Name. DBO. Table Name 11. Obtain all tables in the current Oracle Database Select table_name from user_tables 12. Obtain the types of all fields in the current Oracle table. Select Column_name, data_type, data_length, nullable From User_tab_cols where table_name = 'teacher ';