fetch table name from a column for from clause
@Akshay,
Please find the code below for your reference.
Create or replace procedure create_cursor is
l_statement varchar2(32767);
cursor v_records is
select * from t;
begin
for temp in v_records
loop
l_statement := 'INSERT INTO myTable (id, name) select '||temp.id||','
||temp.name|| ' from ' || temp.table1
|| ' where ' || temp.where_clause;
execute immediate l_statement;
end loop;
end;
/
How can I get column names from a table in SQL Server?
You can obtain this information and much, much more by querying the Information Schema views.
This sample query:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'
Can be made over all these DB objects:
- CHECK_CONSTRAINTS
- COLUMN_DOMAIN_USAGE
- COLUMN_PRIVILEGES
- COLUMNS
- CONSTRAINT_COLUMN_USAGE
- CONSTRAINT_TABLE_USAGE
- DOMAIN_CONSTRAINTS
- DOMAINS
- KEY_COLUMN_USAGE
- PARAMETERS
- REFERENTIAL_CONSTRAINTS
- ROUTINES
- ROUTINE_COLUMNS
- SCHEMATA
- TABLE_CONSTRAINTS
- TABLE_PRIVILEGES
- TABLES
- VIEW_COLUMN_USAGE
- VIEW_TABLE_USAGE
- VIEWS
python library for extracting table names from from clause in SQL Statetments,
You can use sqlparse
with a recursive generator function:
import sqlparse, re
def get_tables(d):
f = False
for i in getattr(d, 'tokens', []):
if isinstance(i, sqlparse.sql.Token) and i.value.lower() == 'from':
f = True
elif isinstance(i, (sqlparse.sql.Identifier, sqlparse.sql.IdentifierList)) and f:
f = False
if not any(isinstance(x, sqlparse.sql.Parenthesis) or 'select' in x.value.lower() for x in getattr(i, 'tokens', [])):
fr = ''.join(str(j) for j in i if j.value not in {'as', '\n'})
for t in re.findall('(?:\w+\.\w+|\w+)\s+\w+|(?:\w+\.\w+|\w+)', fr):
yield {'table':(t1:=t.split())[0], 'alias':None if len(t1) < 2 else t1[-1]}
yield from get_tables(i)
print(list(get_tables(sqlparse.parse(s)[0])))
Output:
[{'table': 'PIMSCS.HISTPREPRO', 'alias': 'OBJ'}, {'table': 'PIMSCS.ESTAGIOS', 'alias': 'B'}, {'table': 'PIMSCS.UPNIVEL3', 'alias': 'UP3'}, {'table': 'PIMSCS.SAFRUPNIV3', 'alias': 'C'}, {'table': 'PIMSCS.VARIEDADES', 'alias': 'D'}, {'table': 'PIMSCS.TIPO_MATURAC', 'alias': 'E'}, {'table': 'PIMSCS.UPNIVEL1', 'alias': 'F'}, {'table': 'PIMSCS.FORNECS', 'alias': 'G'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.SAFRUPNIV3', 'alias': 'D'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': 'OBJ2'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.HISTPREPRO', 'alias': 'OBJ3'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.QUEIMA_HE', 'alias': 'QH'}, {'table': 'PIMSCS.QUEIMA_DE', 'alias': 'QD'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.HISTPREPRO', 'alias': 'A'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.HISTPREPRO', 'alias': 'A2'}, {'table': 'PIMSCS.UPNIVEL3', 'alias': 'A'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.SAFRUPNIV3', 'alias': 'B2'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': 'OBJ'}, {'table': 'PIMSCS.ESTAGIOS', 'alias': 'B'}, {'table': 'PIMSCS.UPNIVEL3', 'alias': 'UP3'}, {'table': 'PIMSCS.SAFRUPNIV3', 'alias': 'C'}, {'table': 'PIMSCS.VARIEDADES', 'alias': 'D'}, {'table': 'PIMSCS.TIPO_MATURAC', 'alias': 'E'}, {'table': 'PIMSCS.UPNIVEL1', 'alias': 'F'}, {'table': 'PIMSCS.FORNECS', 'alias': 'G'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.SAFRUPNIV3', 'alias': 'D'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': 'OBJ2'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.HISTPREPRO', 'alias': 'OBJ3'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.OCORTEMD_DE', 'alias': 'A'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.HISTPREPRO', 'alias': 'A'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.HISTPREPRO', 'alias': 'A2'}, {'table': 'PIMSCS.UPNIVEL3', 'alias': 'A'}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.HISTPREPRO', 'alias': None}, {'table': 'PIMSCS.SAFRUPNIV3', 'alias': 'B2'}]
How to extract table names and column names from sql query?
Really, this is no easy task. You could use a lexer (ply in this example) and define several rules to get several tokens out of a string. The following code defines these rules for the different parts of your SQL string and puts them back together as there could be aliases in the input string. As a result, you get a dictionary (result
) with the different tablenames as key.
import ply.lex as lex, re
tokens = (
"TABLE",
"JOIN",
"COLUMN",
"TRASH"
)
tables = {"tables": {}, "alias": {}}
columns = []
t_TRASH = r"Select|on|=|;|\s+|,|\t|\r"
def t_TABLE(t):
r"from\s(\w+)\sas\s(\w+)"
regex = re.compile(t_TABLE.__doc__)
m = regex.search(t.value)
if m is not None:
tbl = m.group(1)
alias = m.group(2)
tables["tables"][tbl] = ""
tables["alias"][alias] = tbl
return t
def t_JOIN(t):
r"inner\s+join\s+(\w+)\s+as\s+(\w+)"
regex = re.compile(t_JOIN.__doc__)
m = regex.search(t.value)
if m is not None:
tbl = m.group(1)
alias = m.group(2)
tables["tables"][tbl] = ""
tables["alias"][alias] = tbl
return t
def t_COLUMN(t):
r"(\w+\.\w+)"
regex = re.compile(t_COLUMN.__doc__)
m = regex.search(t.value)
if m is not None:
t.value = m.group(1)
columns.append(t.value)
return t
def t_error(t):
raise TypeError("Unknown text '%s'" % (t.value,))
t.lexer.skip(len(t.value))
# here is where the magic starts
def mylex(inp):
lexer = lex.lex()
lexer.input(inp)
for token in lexer:
pass
result = {}
for col in columns:
tbl, c = col.split('.')
if tbl in tables["alias"].keys():
key = tables["alias"][tbl]
else:
key = tbl
if key in result:
result[key].append(c)
else:
result[key] = list()
result[key].append(c)
print result
# {'tb1': ['col1', 'col7'], 'tb2': ['col2', 'col8']}
string = "Select a.col1, b.col2 from tb1 as a inner join tb2 as b on tb1.col7 = tb2.col8;"
mylex(string)
How can I get column names from a table in Oracle?
You can query the USER_TAB_COLUMNS table for table column metadata.
SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'
Get Table Name That Contains A Specific Keyword
You Can Do Like This
$query1 = mysqli_query($your_connection_to_db, "SHOW TABLES FROM movie_tags");
while ($array = mysqli_fetch_array($query1)){
$query2 = mysqli_query($your_connection_to_db, "SELECT * FROM `".$array[0]."` WHERE id = '57'");
$rows = mysqli_num_rows($query2);
if($rows>0){
//you can do your code here with $array[0]. Examplebr> echo "your ".$array[0]." table has 57 in it's id column";
}
}
Get the table name(s) from MySQL database where column name is like X value is equal to Y
Thanks to l.g.karolos I was able to get what I wanted. Here is the code :
DELIMITER $$
DROP PROCEDURE IF EXISTS getStudentFromClassroom$$
CREATE PROCEDURE getStudentFromClassroom(student_name VARCHAR(255))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE _tablename VARCHAR(255);
DECLARE _columnname VARCHAR(255) DEFAULT 'device';
DECLARE _columnNameToFind VARCHAR(255) ;
DECLARE cur1 CURSOR FOR SELECT
CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS table_name,
COLUMN_NAME AS column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('YOUR_COLUMN_NAME')
AND TABLE_SCHEMA='YOUR_TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO _tablename;
IF done THEN
LEAVE read_loop;
END IF;
SET @s = CONCAT('SELECT @student_number := COUNT(DISTINCT ', _columnname,') ',_tablename,' FROM ', _tablename, ' WHERE ', _columnname,' = \'', student_name,'\'');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @s = CONCAT('SELECT @ total_student_number := COUNT(DISTINCT ', _columnname,') ',_tablename,' FROM ', _tablename);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
INSERT INTO getStudentResults VALUES (_tablename,@student_number,@total_student_number);
END LOOP;
CLOSE cur1;
END$$
With getStudentResults declares as
CREATE TEMPORARY TABLE IF NOT EXISTS getStudentResults (
classroom_name VARCHAR(255),
student_number INT DEFAULT 0,
total_student_number INT DEFAULT 0
);
Related Topics
Can SQL Clr Triggers Do This? or Is There a Better Way
Different Value Counts on Same Column
How to Create Foreign Keys Across Databases
Why Are Dot-Separated Prefixes Ignored in the Column List for Insert Statements
Inserting Default Value as Current Date + 30 Days in MySQL
Notify My Wcf Service When My Database Is Updated
SQL How to Search a Many to Many Relationship
Return Value from MySQL Stored Procedure
When Should You Consider Indexing Your SQL Tables
How to Find Fifth Highest Salary in a Single Query in SQL Server
SQL Query - Delete Duplicates If More Than 3 Dups
Replace Unicode Characters in T-Sql
Bigquery SQL: Average, Geometric Mean, Remove Outliers, Median