Tried searching this up myself but couldn’t find anything. If I’m looking for all tables that contain the column CarId and I’m only interested in Id 1, how can I get a list of all tables that match this criteria? This is using Transact SQL.
Tried searching this up myself but couldn’t find anything. If I’m looking for all tables that contain the column CarId and I’m only interested in Id 1, how can I get a list of all tables that match this criteria? This is using Transact SQL.
5条答案
按热度按时间uz75evzq1#
You could do something like this using dynamic sql:
Here we are using the INFORMATION_SCHEMA.COLUMNS to identify all tables containing the particular column you want.
Using that, we then generate a sql query to return only the table names where the
CarID = 1
is found, ignoring all other tables, or even the tables where you have this column, but not the id 1.You can preview the query (on PRINT @SQL) and execute it separately, or you can uncomment EXEC (@SQL) to run all together.
a1o7rhls2#
***Even you have cardid=1 for multiple rows table name will be selected once.
Sample Tables
Query:
Output:
fiddle
l5tcr1uw3#
Only List TableNAmes
all records
eh57zj3b4#
I have a bespoke procedure which I wrote which could do this for you.
It's designed to search for text that can be in any column in any table in any database, one of its options allows you to pass a list of terms to match with (or exclude) column names.
It's a system procedure, you create it in the master database and register it as a system proc, you can then use it within any database context.
For your specific case you would use it like so:
It returns a list of tables where the searched for argument is found, the column(s) containing it and some SQL that you can cut and paste to find rows with the searched for data.
The full code is below. After creating in
master
you would then useexec sys.sp_MS_marksystemobject dbo.sp_FindTextInAnyTable;
There are many customisations possible to limit the searching, some bespoke to the environemnt we use it in such as for optional including temporal table with the assumed history schema etc, parameters are documented in the code below.
Please do try out and let me know if it works for you?
6fe3ivhb5#
run it, copy-paste the results into a new query window, run em