SQL gets 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 ' 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