Reputation: 29769

Find all tables containing column with specified name

Is it possible to query for table names which contain columns being

LIKE '%myName%'

Upvotes: 1802

Answers (30)

bsraskr

Reputation: 630

You can query the system catalogue tables in SQL Server to find tables that contain a column with a specified name pattern. Here’s a query to do that:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%'
ORDER BY TABLE_NAME;

Explanation:

sys.columns --> contains information about all columns in the database.

sys.tables --> provides information about all tables.

The JOIN on object_id --> links each column to its respective table.

WHERE c.name LIKE '%myName%' --> filters for columns containing myName.

ORDER BY t.name sorts --> the results by table name.

Upvotes: 1

AdaTheDev

Reputation: 147334

Search Tables:

SELECT      c.name  AS 'ColumnName'
            ,(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

Search Tables and Views:

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

Upvotes: 2754

Mahum

Reputation: 356

To find all tables containing a column with a specified name in MS SQL Server, you can query the system catalog views. Specifically, you can query the sys.tables and sys.columns views. Here's an example query:

SELECT t.name AS table_name
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'column_name';

Replace column_name with the name of the column you want to search for. This query joins the sys.tables and sys.columns views based on the object_id column and filters the result to retrieve the table names where the column name matches the specified value.

In dbForge Studio for SQL Server, you can use the SQL editor to execute the query mentioned above. It offers features like syntax highlighting, code completion, and result viewing, making it easier to write and execute SQL queries efficiently.

Upvotes: 7

Rinku Choudhary

Reputation: 2261

It will return table_name , schema_name , column_name

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.object_id = c.object_id
WHERE c.name LIKE '%colName%'
ORDER BY schema_name,
table_name;

Upvotes: 15

Gudwlk

Reputation: 1157

SELECT table_schema + '.' + table_name,
       column_name
FROM   [yourdatabase].information_schema.columns
WHERE  column_name LIKE '%myName%'

This will give you the table name of the column that you need to find.

Upvotes: 38

hardik rawal

Reputation: 115

You can use the query below in SQL Server.

SELECT column_name AS 'ColumnName',
       table_name  AS 'TableName'
FROM   information_schema.columns
WHERE  column_name LIKE '%columnname%'
       AND table_schema = 'schema'
ORDER  BY tablename,
          columnname;

Upvotes: -1

Majedur

Reputation: 3242

In addition, you can find column names with specified schema also.

SELECT 'DBName' AS DBName,
       column_name,
       table_name,
       table_schema
FROM   dbname.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%'
       AND table_schema IN ( 'YourSchemaName' )

You can also find the same column on multiple databases.

SELECT 'DBName1' AS DB,
       column_name,
       table_name,
       table_schema
FROM   dbname1.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%'
UNION
SELECT 'DBName2' AS DB,
       column_name,
       table_name,
       table_schema
FROM   dbname2.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%'

Upvotes: 1

user3583912

Reputation: 1322

I don't know why many of you suggest joining with sys.table with sys.columns.

You can use the below code:

SELECT Object_name(object_id) AS TableName,
       *
FROM   sys.columns
WHERE  NAME LIKE '%MyName%'

If you want schema name as well:

SELECT *
FROM   information_schema.columns
WHERE  column_name LIKE '%MyName%'

Upvotes: 64

Saurabh Rana

Reputation: 3548

This simple query worked for me.

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'schemaName'
AND column_name like '%model%';

Upvotes: 9

Goutam

Reputation: 423

In MS SQL, you can write the below line to check the column names of a particular table:

sp_help your_tablename

Or, you can first select your table name in the query windows (highlight the schema and table name) and then press key combination below:

Alt + F1

Upvotes: 2

dev_2014

Reputation: 371

In MS SQL Server Database, use this query to get the tables and respective column names that contains the input text:

SELECT t.name AS tableName, c.name AS columnName 
FROM sys.tables as t 
INNER JOIN sys.columns AS c ON t.object_id=c.object_id 
WHERE c.name LIKE '%<your_search_string>%'

Upvotes: 17

Yogendra

Reputation: 534

Here is the answer to your question

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

Upvotes: 30

MarredCheese

Reputation: 20881

I wanted something for tables and views that didn't make my eyes bleed.

Query

SELECT
    t.TABLE_TYPE AS [Type],
    c.TABLE_NAME AS [Object],
    c.COLUMN_NAME AS [Column]
FROM
    INFORMATION_SCHEMA.COLUMNS AS c
    LEFT JOIN INFORMATION_SCHEMA.TABLES AS t ON
        t.TABLE_CATALOG = c.TABLE_CATALOG AND 
        t.TABLE_SCHEMA = c.TABLE_SCHEMA AND
        t.TABLE_NAME = c.TABLE_NAME
WHERE
    c.COLUMN_NAME LIKE '%myName%'
ORDER BY
    [Type],
    [Object],
    [Column]

Result

Type        Object   Column
----------------------------
BASE TABLE  Table1   myName1
BASE TABLE  Table2   myName2
VIEW        View1    myName1
VIEW        View2    myName2

Upvotes: 15

Raj

Reputation: 111

SQL query to show all tables that have specified column name:

SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
  FROM sys.tables t
 INNER JOIN sys.columns c ON c.object_id = t.object_id
 WHERE c.name like '%ColumnName%'
 ORDER BY 'Table Name'

Upvotes: 4

Sheikh Abdul Wahid

Reputation: 2773

We can use sp_columns for the purpose.

sp_columns 'table name', null, null, '%column name%'

Upvotes: 2

Francis Rodgers

Reputation: 4685

select  
        s.[name]            'Schema',
        t.[name]            'Table',
        c.[name]            'Column',
        d.[name]            'Data Type',
        c.[max_length]      'Length',
        d.[max_length]      'Max Length',
        d.[precision]       'Precision',
        c.[is_identity]     'Is Id',
        c.[is_nullable]     'Is Nullable',
        c.[is_computed]     'Is Computed',
        d.[is_user_defined] 'Is UserDefined',
        t.[modify_date]     'Date Modified',
        t.[create_date]     'Date created'
from        sys.schemas s
inner join  sys.tables  t
on s.schema_id = t.schema_id
inner join  sys.columns c
on t.object_id = c.object_id
inner join  sys.types   d
on c.user_type_id = d.user_type_id
where c.name like '%ColumnName%'

This here will give you a little extra information about the schema, tables and columns that you may or may not choose to use extra conditions in your where clause to filter on. For example, if you only wanted to see the fields which must have values add

and c.is_nullable = 0

You could add other conditionals, I also added the columns in the select clause in this vertical manner so it was easy to reorder, remove, rename, or add others based on your needs. Alternately you could search for just tables by using T.Name. Its very customisable.

Enjoy.

Upvotes: 121

Todd Vlk

Reputation: 2609

SQL Server:

SELECT Table_Name, Column_Name 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'YOUR_DATABASE'
AND COLUMN_NAME LIKE '%YOUR_COLUMN%'

Oracle:

SELECT owner, table_name, column_name 
FROM all_tab_columns 
WHERE column_name LIKE '%YOUR_COLUMN_NAME%'
AND OWNER IN ('YOUR_SCHEMA_NAME');

SIMPLE AS THAT!! (SQL, PL/SQL)
I use it ALL the time to find ALL instances of a column name in a given database (schema).

Upvotes: 244

Janey

Reputation: 1310

Here's a working solution for a Sybase database

select 
  t.table_name, 
  c.column_name 
from 
  systab as t key join systabcol as c 
where 
   c.column_name = 'MyColumnName'

Upvotes: 2

Ste Bov

Reputation: 856

If you simply want the table name you can run:

select object_name(object_id) from sys.columns
where name like '%received_at%'

If you want the Schema Name as well (which in a lot of cases you will, as you'll have a lot of different schemas, and unless you can remember every table in the database and where it belongs this can be useful) run:

select OBJECT_SCHEMA_NAME(object_id),object_name(object_id) from sys.columns
where name like '%received_at%'

and finally if you want it in a nicer format (although this is where the code (In my opinion) is getting too complicated for easy writing):

select concat(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) from sys.columns
where name like '%received_at%'

note you can also create a function based on what I have:

CREATE PROCEDURE usp_tablecheck
--Scan through all tables to identify all tables with columns that have the provided string
--Stephen B
@name nvarchar(200)
AS
SELECT CONCAT(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) AS [Table Name], name AS [Column] FROM sys.columns
WHERE name LIKE CONCAT('%',@name,'%')
ORDER BY [Table Name] ASC, [Column] ASC
GO

It is worth noting that the concat feature was added in 2012. For 2008r2 and earlier use + to concatenate strings.

I've re-formatted the proc a bit since I posted this. It's a bit more advanced now but looks a lot messier (but it's in a proc so you'll never see it) and it's formatted better.

This version allows you to have it in an administrative database and then search through any database. Change the decleration of @db from 'master' to whichever you want the default database to be (NOTE: using the CONCAT() function will only work with 2012+ unless you change the string concatenation to use the + operators).

CREATE PROCEDURE [dbo].[usp_tablecheck]
    --Scan through all tables to identify all tables in the specified database with columns that have the provided string
    --Stephen B
    @name nvarchar(200)
    ,@db nvarchar(200) = 'master'
AS
    DECLARE @sql nvarchar(4000) = CONCAT('
        SELECT concat(OBJECT_SCHEMA_NAME(col.object_id,DB_ID(''',@db,''')),''.'',object_name(col.object_id,DB_ID(''',@db,'''))) AS [Table Name]
            ,col.name AS [Column] 
        FROM ',@db,'.sys.columns col
        LEFT JOIN ',@db,'.sys.objects ob 
            ON ob.object_id = col.object_id
        WHERE 
            col.name LIKE CONCAT(''%'',''',@name,''',''%'') 
            AND ob.type =''U''
        ORDER BY [Table Name] ASC
            ,[Column] ASC')
    EXECUTE (@sql)
GO

Upvotes: 44

soheila sadeghian

Reputation: 91

You can try this query:

USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%myName%'

Upvotes: 7

Munavvar

Reputation: 821

You can find it from INFORMATION_SCHEMA.COLUMNS by column_name filter

Select DISTINCT TABLE_NAME as TableName,COLUMN_NAME as ColumnName
     From INFORMATION_SCHEMA.COLUMNS Where column_name like '%myname%'

Upvotes: 26

Dwoolk

Reputation: 1501

If you’re more into third party tools there a lot of options there such as:

These come in very handy if your database contains encrypted objects (views, procedures, functions) because you can’t easily search for these using system tables.

Upvotes: 64

Kaleab

Reputation: 251

To get full information: column name, table name as well as schema of the table..

SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%col_Name%'

Upvotes: 24

DataWrangler

Reputation: 2165

Create table #yourcolumndetails(
DBaseName varchar(100), 
TableSchema varchar(50), 
TableName varchar(100),
ColumnName varchar(100), 
DataType varchar(100), 
CharMaxLength varchar(100))

EXEC sp_MSForEachDB @command1='USE [?];
    INSERT INTO #yourcolumndetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME like ''origin'''

select * from #yourcolumndetails
Drop table #yourcolumndetails

Upvotes: 5

Iceknight

Reputation: 113

Like oracle you can find tables and columns with this:

select table_name, column_name
from user_tab_columns 
where column_name 
like '%myname%';

Upvotes: 7

Pedro S Cord

Reputation: 1359

SELECT t.name AS table_name, 
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name
FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Label%'
ORDER BY schema_name, table_name;

Upvotes: 7

Stephen Brett

Reputation: 59

Just to improve on the answers above i have included Views as well and Concatenated the Schema and Table/View together making the Results more apparent.

DECLARE @COLUMNNAME AS VARCHAR(100);

SET @COLUMNNAME = '%Absence%';

SELECT CASE
           WHEN [T].[NAME] IS NULL
           THEN 'View'
           WHEN [T].[NAME] = ''
           THEN 'View'
           ELSE 'Table'
       END AS [TYPE], '[' + [S].[NAME] + '].' + '[' + CASE
                                                          WHEN [T].[NAME] IS NULL
                                                          THEN [V].[NAME]
                                                          WHEN [T].[NAME] = ''
                                                          THEN [V].[NAME]
                                                          ELSE [T].[NAME]
                                                      END + ']' AS [TABLE], [C].[NAME] AS [COLUMN]
FROM [SYS].[SCHEMAS] AS [S] LEFT JOIN [SYS].[TABLES] AS [T] ON [S].SCHEMA_ID = [T].SCHEMA_ID
                            LEFT JOIN [SYS].[VIEWS] AS [V] ON [S].SCHEMA_ID = [V].SCHEMA_ID
                            INNER JOIN [SYS].[COLUMNS] AS [C] ON [T].OBJECT_ID = [C].OBJECT_ID
                                                                 OR
                                                                 [V].OBJECT_ID = [C].OBJECT_ID
                            INNER JOIN [SYS].[TYPES] AS [TY] ON [C].[SYSTEM_TYPE_ID] = [TY].[SYSTEM_TYPE_ID]
WHERE [C].[NAME] LIKE @COLUMNNAME
GROUP BY '[' + [S].[NAME] + '].' + '[' + CASE
                                             WHEN [T].[NAME] IS NULL
                                             THEN [V].[NAME]
                                             WHEN [T].[NAME] = ''
                                             THEN [V].[NAME]
                                             ELSE [T].[NAME]
                                         END + ']', [T].[NAME], [C].[NAME], [S].[NAME]
ORDER BY '[' + [S].[NAME] + '].' + '[' + CASE
                                             WHEN [T].[NAME] IS NULL
                                             THEN [V].[NAME]
                                             WHEN [T].[NAME] = ''
                                             THEN [V].[NAME]
                                             ELSE [T].[NAME]
                                         END + ']', CASE
                                                        WHEN [T].[NAME] IS NULL
                                                        THEN 'View'
                                                        WHEN [T].[NAME] = ''
                                                        THEN 'View'
                                                        ELSE 'Table'
                                                    END, [T].[NAME], [C].[NAME];

Upvotes: 4

Rainhider

Reputation: 836

I used this for the same purpose and it worked:

  select * from INFORMATION_SCHEMA.COLUMNS
  where TABLE_CATALOG= 'theDatabase'
  and COLUMN_NAME like 'theCol%'

Upvotes: 7

Khwaza Bandenawaz

Reputation: 5615

We can also use the following syntax:-

select * from INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME like '%clientid%' 
order by TABLE_NAME

Upvotes: 559

cichy

Reputation: 10644

This should work:

SELECT name 
FROM sysobjects 
WHERE id IN ( SELECT id 
              FROM syscolumns 
              WHERE name like '%column_name%' )

Upvotes: 134

Find all tables containing column with specified name

Answers (30)

Related Questions