SQL

Simple postgres SQL query to search for a column name within a DB schema

26 November 2013

The query is as simple as that:

SELECT *
FROM information_schema.columns
WHERE column_name = ‘search_for_me’

SQL to query equally named colons on DB

17 April 2013

Handy dandy SQL query to find non-foreign-key related searches:

SELECT
TABLE_TYPE,
isColumns.TABLE_NAME,
COLUMN_NAME,
COLUMN_TYPE,
COLUMN_KEY,
IS_NULLABLE,
COLUMN_DEFAULT
FROM
INFORMATION_SCHEMA.COLUMNS as isColumns
inner join INFORMATION_SCHEMA.TABLES as isTables on ( isColumns.table_name = isTables.table_name )
WHERE
isColumns.table_schema = ‘YOURDATABASA’ AND
column_name LIKE ‘%WILD%’;

SQL variable in a statement, select all table fields in a single result *

28 February 2013

Here is a short SQL statement that could save you a lot of time if you are trying to get an unknown set of fields from a given table in a single result.

SET @fields = (SELECT GROUP_CONCAT( `COLUMN_NAME`)
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = ‘YOURDB’ AND `TABLE_NAME` = ‘YORTABLE’ GROUP BY `TABLE_NAME` )
COLLATE utf8_general_ci;
set @sql = CONCAT(‘SELECT GROUP_CONCAT(‘,@fields, ‘) FROM YORTABLE LIMIT 1’);
PREPARE test_sql FROM @sql;
execute test_sql;

Categories