The mysql database:
Such as a table: sr_problem_process,
This table has two hundred fields,
Some fields not used (i.e., all values of this field is null),
Query out all the fields,
Using SQL or stored procedures,
Pray god to help write, thanks

CodePudding user response:

To information_schema. COLUMNS table with concat joining together a bunch of SQL statement to execute

CodePudding user response:

 
DROP PROCEDURE 
IF the EXISTS find_null_col; 

CREATE PROCEDURE find_null_col (
IN tableName VARCHAR (20), 
IN dbName VARCHAR (20), 
OUT the result VARCHAR (200) 
) - create a stored procedure named tests 
The BEGIN 

DECLARE col_name VARCHAR (50); 


DECLARE no_more_record TINYINT DEFAULT 0; 


DECLARE rs_cursor CURSOR FOR the SELECT 
COLUMN_NAME 
The FROM 
INFORMATION_SCHEMA. COLUMNS 
WHERE 
Table_name=@ tableName 
AND table_schema=@ dbName; 


DECLARE the CONTINUE HANDLER FOR the NOT FOUND 
The SET no_more_record=1; 

The OPEN rs_cursor; 


The SET @ tableName=CONCAT (tableName); 


The SET @ dbName=CONCAT (dbName); 


REPEAT 
The FETCH rs_cursor INTO col_name; 


IF NOT no_more_record THEN 

The SET @ sqlStr=CONCAT (
'the select 1 from' 
@ tableName, 
'where', 
Col_name, 
'is not null limit 1 
); 

PREPARE STMT 
The FROM 
@ sqlStr; 

- predefined a statement and to assign it to STMT 
The EXECUTE STMT. 


IF FOUND_ROWS ()=0 THEN 
SELECT 
CONCAT_WS (', ', the result, col_name) INTO the result. 


END 
IF; 

- execute statement 
DEALLOCATE PREPARE STMT. 

A prepared statement - to release resources 
END 
IF; 

UNTIL no_more_record 
END 
REPEAT 
; 

The CLOSE rs_cursor; 


END; 

CALL find_null_col (' sr_problem_process ', 'db_problem' @ test); 
SELECT @ test; 

CodePudding user response:

refer to the second floor hongmei85 response:

 
DROP PROCEDURE 
IF the EXISTS find_null_col; 

CREATE PROCEDURE find_null_col (
IN tableName VARCHAR (20), 
IN dbName VARCHAR (20), 
OUT the result VARCHAR (200) 
) - create a stored procedure named tests 
The BEGIN 

DECLARE col_name VARCHAR (50); 


DECLARE no_more_record TINYINT DEFAULT 0; 


DECLARE rs_cursor CURSOR FOR the SELECT 
COLUMN_NAME 
The FROM 
INFORMATION_SCHEMA. COLUMNS 
WHERE 
Table_name=@ tableName 
AND table_schema=@ dbName; 


DECLARE the CONTINUE HANDLER FOR the NOT FOUND 
The SET no_more_record=1; 

The OPEN rs_cursor; 


The SET @ tableName=CONCAT (tableName); 


The SET @ dbName=CONCAT (dbName); 


REPEAT 
The FETCH rs_cursor INTO col_name; 


IF NOT no_more_record THEN 

The SET @ sqlStr=CONCAT (
'the select 1 from' 
@ tableName, 
'where', 
Col_name, 
'is not null limit 1 
); 

PREPARE STMT 
The FROM 
@ sqlStr; 

- predefined a statement and to assign it to STMT 
The EXECUTE STMT. 


IF FOUND_ROWS ()=0 THEN 
SELECT 
CONCAT_WS (', ', the result, col_name) INTO the result. 


END 
IF; 

- execute statement 
DEALLOCATE PREPARE STMT. 

A prepared statement - to release resources 
END 
IF; 

UNTIL no_more_record 
END 
REPEAT 
; 

The CLOSE rs_cursor; 


END; 

CALL find_null_col (' sr_problem_process ', 'db_problem' @ test); 
SELECT @ test; 

There is an error in the execution

CodePudding user response:

What is wrong?

'db_problem into your actual database name

OUT the result VARCHAR (200) increase the length of the VARCHAR according to actual condition, such as the OUT result VARCHAR (2000).

CodePudding user response:

reference 4 floor hongmei85 response:

what wrong?

'db_problem into your actual database name

OUT the result VARCHAR (200) according to the actual situation to increase the length of the VARCHAR, such as the OUT result VARCHAR (2000)

Create a stored procedure, directly submitted to the grammatical errors

CodePudding user response:

reference 4 floor hongmei85 response:

what wrong?

'db_problem into your actual database name

OUT the result VARCHAR (200) according to the actual situation to increase the length of the VARCHAR, such as the OUT result VARCHAR (2000)

The great god, for help...