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

Table filter by column name

paolo.sanchi posted 1 year ago in Feature discussion
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!
paolo.sanchi posted 1 year ago
sorry for the big amount of grammatical errors unhappy
ansgar posted 1 year ago
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%'

paolo.sanchi posted 1 year ago
i'm connected to a SQLServerhappy
MichaelM posted 1 year ago
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
paolo.sanchi posted 1 year ago
great! anyway, I think it would be a usefull, and cheap to implement, feature. Don't you?
ansgar posted 1 year ago
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.