Below is a simple solution as to how you can find tables from a database that have two (or more) specific columns. I have divided 3 different scenarios for you to use. They are seemingly out of order, but I have set it up specifically to first show the code to search for 2+ columns that exists in one table. This is probably one of the most useful statement(s) I have ever written, I hope you find it useful!
Primary Reference:
DB_NAME = This function returns the name of a specified database
SCHEMA_NAME = Returns the schema name associated with a schema ID
SYS.COLUMNS = Returns a row for each column of an object that has columns, such as views or tables
SYS.TABLES = Returns a row for each user table in SQL Server
SYS.VIEWS = Contains a row for each view object, with sys.objects.type = V
LIKE = Used to compare a value to similar values using the wildcard operators
% = Matches one or more characters
Scenario 1: Look for tables that have both columns: Like Order Date and Amount.
Scenario 2: Look for tables that have these 3 columns: Like Order Date, Amount, Name.
To do this all I added was another join and expanded the 'ON' statement. It is important to expand, this will eliminate potential duplicates.
Scenario 3: Look for tables that have this column: Like Name
This is the simplest form of lookup as it only looks for tables that have 1 criteria.
CODE:
/* Query Name: SQL Search for columns that exists in one table
Created By: LearnwithGlezy.com*/
USE Sample --- Update with Database Name
SELECT
dbname = DB_NAME(),
schma = SCHEMA_NAME(a.schema_id),
tbl = a.name,
col1 = b.name,
test = CONCAT('select top 10 * from ',DB_NAME(),'.',SCHEMA_NAME(a.schema_id),'.',a.name)
FROM sys.tables a
JOIN sys.columns b ON a.object_id = b.object_id
WHERE 1 = 1
AND b.name like '%amount%' -- Update with '%nameofcolumn%'
IMPORTANT: This query will only show result from tables. If you only have access to 'Views' then change sys.tables to sys.views.
What to Study:
SYS OBJECT CATALOG VIEWS:
Comments