Find SQL Table Name with a Particular Column

Find all table names with column name?

Please try the below query. Use sys.columns to get the details :-

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyCol%';

find sql table name with a particular column

In SQL Server, you can query sys.columns.

Something like:

 SELECT
     t.name
 FROM
     sys.columns c
        inner join
     sys.tables t
        on
           c.object_id = t.object_id
 WHERE
     c.name = 'NameID'

You might want an additional lookup to resolve the schema name, if you have tables in multiple schemas.

Search of table names

I'm using this and works fine

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%%'

How to find all tables and datasets/databases which have a specific column name in big query

I found the solution is to replace the dataset name with region-us instead.

The below works for looking up across tables and datasets

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.TABLES
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%' 

The below works for views:

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.VIEWS
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%' 

How to find specific column in SQL Server database?

You can query the database's information_schema.columns table which holds the schema structure of all columns defined in your database.

Using this query:

select * from information_schema.columns where column_name = 'ProductNumber'

The result would give you the columns:

TABLE_NAME, TABLE_CATALOG, DATA_TYPE and more properties for this database column.

How can I get column names from a table in SQL Server?

You can obtain this information and much, much more by querying the Information Schema views.

This sample query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

Can be made over all these DB objects:

• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMN_PRIVILEGES
• COLUMNS
• CONSTRAINT_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• DOMAIN_CONSTRAINTS
• DOMAINS
• KEY_COLUMN_USAGE
• PARAMETERS
• REFERENTIAL_CONSTRAINTS
• ROUTINES
• ROUTINE_COLUMNS
• SCHEMATA
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• TABLES
• VIEW_COLUMN_USAGE
• VIEW_TABLE_USAGE
• VIEWS

How to find a table name which's column name consist cl_

ALL_TAB_COLS would give you the required details.

For example, I add a new table T to SCOTT schema, with column name as EMP_ID, I expect only 1 row in output for column name like 'EMP_%'

Let's see -

Edit Forgot to ESCAPE underscore.

SQL> CREATE TABLE t(EMP_ID NUMBER);

Table created.

SQL>
SQL> SELECT table_name, column_name
  2  FROM all_tab_cols
  3  WHERE owner='SCOTT'
  4  AND column_name LIKE 'EMP\_%' ESCAPE '\';

TABLE_NAME           COLUMN_NAME
-------------------- --------------
T                    EMP_ID

SQL>

---

Related Topics