-1

In SQL Server, I'm trying to find all of the tables that contain a certain account number. Many of the tables, use the column name 'account'. And I'm trying to find all of the tables where account = '123'.

I'm using the following code to get all of the tables by name, and I already know the column name, but I can't seem to be able to use the table name from the results.

select * from 
(
SELECT
  sys.columns.name AS ColumnName,
  tables.name AS TableName
FROM
  sys.columns
JOIN sys.tables ON
  sys.columns.object_id = tables.object_id
WHERE
  sys.columns.name = 'account'
) as tn 
where TableName.account = '123'
John
  • 1,310
  • 3
  • 32
  • 58

1 Answers1

1

You have two issues to overcome.

First, the SQL language has a very strict rule that you need to be able to know about all tables and other objects used in the query up front, at query compile time, before looking at any data.

To understand this, remember that databases are built to enforce strict access permissions, and these permissions are evaluated at query compile time. If you have to look inside table data after running part of the query to know which objects you will access, the permission evaluation step will not be able to do it's job correctly.

The other issue is the schema for the result set. After we get past the first issue you'll presumably find many different tables, and each of those tables will have different schemas. What, then, does the result set look like? (If it turns out they all have the same schema, you have a different -- but still serious -- problem)

Instead, you'll have to do this as dynamic SQL, where you get this data over three steps:

  1. Run a query to find your table names.
  2. Use the results from step 1 to dynamically build new SQL statements
  3. Run the statements built in step 2 as separate queries.
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794