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 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 ');
SQL gets all database names, table names, stored procedures, and parameter lists