-1

In a typical query, you have to declare a column in the where statement.

Select *
From TABLE 
Where ColumnName like '%Some Value%'

I would like to scale my query to execute against every column, in every table.

More Info: I'm working with a database that has ~1,000 tables, and each table has 5-100 columns each. I'm attempting to check for special characters throughout the entirety of the db. My existing query works, but it's per column, and would be very hard to write for every column in every table.

SELECT *
FROM Table
WHERE ColumnName '%[^a-z0-9 ._-]%'

This has likely been addressed as it can't be unique, but I've yet to find a post that answered it directly (usually it's an adaptation in some form). If there's a post that does capture this please feel free to link - I just haven't found one that quite covered it. It seems like it may be an adaptation of 'search database for string' scripts, but I haven't seen one of these that I was able to adapt to include the where logic I'm using.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Fuzz Evans
  • 2,893
  • 11
  • 44
  • 63
  • Your wording suggests to you want to search every column, but your example SQL doesn't show that. You need to build a dynamic query by querying the system table to find every table and every column - and yes it has been answered before but fair to say its hard to find these duplicates. – Dale K Sep 09 '22 at 22:29
  • That said a quick google for "sql server search every column in every table" returns https://stackoverflow.com/questions/436351/find-a-value-anywhere-in-a-database which looks like it might do what you ask. – Dale K Sep 09 '22 at 22:30
  • sys.columns containts all columns of all objects. Also you can filter by name or OBJECT_NAME(object_id) – Meyssam Toluie Sep 10 '22 at 05:06

1 Answers1

0

try below query for get all columns from all db tables

select c.COLUMN_NAME,c.DATA_TYPE,t.name from sys.objects t
join
INFORMATION_SCHEMA.COLUMNS c on c.TABLE_NAME=t.name
where type='U'
desertnaut
  • 57,590
  • 26
  • 140
  • 166
Atul Pathrikar
  • 1
  • 3