distal-attribute
distal-attribute
distal-attribute
distal-attribute

Table filter by column name

User, date Message
Written by paolo.sanchi
10 months ago
Category: Feature discussion
6 posts since Thu, 04 Jul 13
I really like the new feature to quickly filter the databases and tables by name.
Another cool filter would be a filter by column.
Some time happens to ask something like this: "What is the table tha has the column password?" the answere could be "Some tables has it" :).
Is there a way to do something like this? If not, it would be a great feature to implement!
Written by paolo.sanchi
10 months ago
6 posts since Thu, 04 Jul 13
sorry for the big amount of grammatical errors unhappy
Written by ansgar
10 months ago
4801 posts since Fri, 07 Apr 06
There is a simple query you can fire against the information_schema database:

SELECT * FROM information_schema.`COLUMNS` c
WHERE c.COLUMN_NAME LIKE '%password%'

Written by paolo.sanchi
10 months ago
6 posts since Thu, 04 Jul 13
i'm connected to a SQLServerhappy
Written by MichaelM
10 months ago
1 posts since Fri, 05 Jul 13
The above query also works on SQL Server (>=2005) if you remove the quotes around COLUMNS. However it will only check in the current database (add a suitable USE xyz; to make sure you're in the correct db).

Here's a way to query all tables in all databases:
SET NOCOUNT ON
DECLARE @AllTables table (TABLE_CATALOG sysname,TABLE_SCHEMA sysname, TABLE_NAME sysname, COLUMN_NAME sysname)
DECLARE
@SQL nvarchar(4000)
SET @SQL='SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM [?].information_schema.COLUMNS c WHERE c.COLUMN_NAME LIKE ''%password%'''

INSERT INTO @AllTables (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,COLUMN_NAME)
EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,COLUMN_NAME
Written by paolo.sanchi
9 months ago
6 posts since Thu, 04 Jul 13
great! anyway, I think it would be a usefull, and cheap to implement, feature. Don't you?
Written by ansgar
9 months ago
4801 posts since Fri, 07 Apr 06
This is not something I will put into HeidiSQL as a new feature. The above hints were meant to help you out for finding the wanted column by firing a query.
 

Please login to leave a reply, or register at first.