sql获取所有数据库名、表名、储存过程以及参数列表（SQL gets all database names, table names, stored procedures, and parameter lists）

sql获取所有数据库名、名、储存过程以及参数列表（SQL gets all database names, table names, stored procedures, and parameter lists） sql获取所有数据库名、表名、储存过程以及参数列表(SQL gets all database names, table names, stored procedures, and parameter lists) 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'stands for accounts Islogin='0'stands for roles Status='2'represents a user account Status='0'said the 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': stands for 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,, keyno, from, sysindexkeys,, where, id=Object_Id (table name)) [integrated network data processing] 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': stands for all system tables; (2) SELECT, name, FROM, sysobjects, WHERE, type ='U', AND, sysstat ='83' Note: in general, you only need type ='U', but sometimes the system tables are mixed (not knowing why), and you can delete these system tables after the next 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') " Watch out: (a) here, in order to highlight some important content, select several of these outputs. (b) a data type number table containing syscolumns, to get the full name from the systypes table to find better corresponding to general users to use the data types for xusertype, no one to many situation. (c) syscolumns.length gets the length of the physical memory, so the display of type such as nvarchar and varchar in the database is half of this. 4. Get the column name included in the table primary key: SELECT syscolumns.name FROM syscolumns, sysobjects, sysindexes, sysindexkeys WHERE syscolumns.id ('tablename') = object_id 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 the 4 system tables. The relationship is complex and can be roughly represented as: Syscolumns has a column in a table and information table ID, sysobjects table has a primary key name (that is similar to the PK_Table) and ID, are key names in sysindexes and table ID and index numbers, sysindexkeys ID and index are stored in the table number and column number, You can find the column names after one item matches each other. 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 this way, the result is the same: Select, syscolumns.name, systypes.name, syscolumns.length, from, syscolumns, systypes Where (syscolumns.id=object_id ('order master file'), and, syscolumns.xusertype=systypes.xusertype) Order by syscolumns.colorder; Go Execution results: (fields appear only once, normal) The order number is int 4 Customer number nvarchar 10 Employee number int 4 Order date datetime 8 Date of shipment is datetime 8 Delivery date datetime 8 Delivery mode int 4 Freight money 8 Consignee nvarchar 80 Delivery address nvarchar 120 Delivery city nvarchar 30 Delivery administration nvarchar 30 Delivery zip code nvarchar 20 Deliver country zone 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 results: (partial fields appear two times, data types are different) The order number is int 4 Customer number nvarchar 10 Customer number sysname 10 Employee number int 4 Order date datetime 8 Order date date of birth type 8 Date of shipment is datetime 8 Date of shipment, date of birth type 8 Delivery date datetime 8 Date of delivery date of birth type 8 Delivery mode int 4 Freight money 8 Freight type 8 Consignee nvarchar 80 Consignee sysname 80 Delivery address nvarchar 120 Delivery address sysname 120 Delivery city nvarchar 30 Delivery City sysname 30 Delivery administration nvarchar 30 Delivery administration sysname 30 Delivery zip code nvarchar 20 Delivery zip code sysname 20 Deliver country zone nvarchar 30 Deliver country zone sysname 30 Query stored procedures DepartmentSalaryInfo all the information contained in the system view syscolumns, systypes 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 results: North wind trade; dbo DepartmentSalaryInfo; 1 @RETURN_VALUE 040; NULL 03; NULL NULL; NULL int NULL int 北风贸易DBO departmentsalaryinfo;1 @部1 1 0 1 129 10 10空空空空varchar varchar 北风贸易DBO departmentsalaryinfo;1 @ 2 2 0 1 6平均空空空空空19钱 北风贸易DBO departmentsalaryinfo;1 @ 3 2 0 1 6最大空空空空空19钱 北风贸易DBO departmentsalaryinfo;1最低4 2 0零1 @ 6零19零钱 ——存储过程中的参数名，参数类型，参数长度 选择syscolumns.name，systypes.name，syscolumns.length从列 左连接systypes中存在对xusertype = systypes.xusertype列。 其中ID =(选择ID从sysobjects WHERE name = 'departmentsalaryinfo”); 1:获取当前数据库中的所有用户表 从中选择名称，xtype = U和状态> = 0 2:获取某一个表的所有字段 选择名称列的id是object_id('表名”) 3:查询用户创建的所有数据库 SELECT * FROM ..中D，希德没有(选择Sid 主.. syslogins name = 'sa”) 或者 选择DBID，名称为db_name大师中，希德,>头.. 4:查询某一个表的字段和数据类型 选择column_name，data_type从information_schema.columns 在table_name =“表名” [ ] [标]: SELECT * FROM的表名为客 [ ] [标题]: 8。如何修改数据库的名称: sp_renamedb”old_name '，' new_name” 9。只复制一个表结构，不复制数据 选择前0 *为[ ] [ ]从T1，T2 10。连接远程数据库 SELECT * FROM OPENDATASOURCE('sqloledb，数据源=远程用户IP; ID = SA;密码=密码”)。库名。表名dbo。 11。获取当前Oracle数据库中的所有表 选择table_name从user_tables 12。获取当前Oracle表中所有字段的类型 选择 column_name，data_type，data_length，空 从 user_tab_cols哪里table_name =“”