name - sql query to find tables with a specific value in any column




Find all tables containing column with specified name-MS SQL Server (20)

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

LIKE '%myName%'

?


Search Tables:

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%'
ORDER BY    TableName
            ,ColumnName;

Search Tables & Views:

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

Following query will give you the exact table names of the database having field name like '%myName'.

SELECT distinct(TABLE_NAME)
  FROM INFORMATION_SCHEMA.COLUMNS    
 WHERE COLUMN_NAME LIKE '%myName%'

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'

Hopefully this isn't a duplicate answer, but what I like to do is generate a sql statement within a sql statement that will allow me to search for the values I am looking for (not just the tables with those field names ( as it's usually necessary for me to then delete any info related to the id of the column name I am looking for):

  SELECT  'Select * from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'

Then I can copy and paste run my 1st column "SQLToRun"... then I replace the "Select * from ' with 'Delete from ' and it allows me to delete any references to that given ID! Write these results to file so you have them just in case.

NOTE**** Make sure you eliminate any bakup tables prior to running your your delete statement...

  SELECT  'Delete from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'

I used this for the same purpose and it worked:

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

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

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];

Like oracle you can find tables and columns with this:

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

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%'

We can also use the following syntax:-

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

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%'


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

DECLARE @columnName as varchar(100)
SET @columnName = 'ColumnName'

SELECT t.name AS Table, c.name AS Column,
ty.name AS Type, c.max_length AS Length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.name LIKE @columnName
ORDER BY t.name, c.name

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

SELECT col.Name AS ColumnName, tab.Name AS TableName 
FROM sys.columns col  
     JOIN sys.tables tab   
ON col.Object_id = tab.Object_id   
WHERE col.Name LIKE '%MyName%'

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).

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 '%EmployeeID%'
ORDER BY schema_name, table_name; 

It is from Pinal Sir Blog


select  
        s.[name]            'Schema',
        t.[name]            'Table',
        c.[name]            'Column',
        d.[name]            'Data Type',
        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.


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

It works





system-tables