Найти все таблицы, содержащие столбец с указанным именем - MS SQL Server

Можно ли запрашивать имена таблиц, которые содержат столбцы, являющиеся

 LIKE '%myName%'
 

?

вопрос задан 31.01.2011
gruber
9446 репутация

29 ответов


  • 1427 рейтинг

    Таблицы поиска:

     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;
     

    Поиск таблиц и просмотров:

     SELECT      COLUMN_NAME AS 'ColumnName'
                ,TABLE_NAME AS  'TableName'
    FROM        INFORMATION_SCHEMA.COLUMNS
    WHERE       COLUMN_NAME LIKE '%MyName%'
    ORDER BY    TableName
                ,ColumnName;
     
    ответ дан AdaTheDev, с репутацией 102996, 31.01.2011
  • 234 рейтинг

    Мы также можем использовать следующий синтаксис:

     select * from INFORMATION_SCHEMA.COLUMNS 
    where COLUMN_NAME like '%clientid%' 
    order by TABLE_NAME
     
    ответ дан Khwaza Bandenawaz, с репутацией 2349, 18.10.2011
  • 81 рейтинг
     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');
     
    • ПРОСТО КАК ТОТ!! (SQL, PL / SQL) Я использую его ВСЕ время, чтобы найти ВСЕ экземпляры имени столбца в данной базе данных (схеме).
    ответ дан Todd ECU, с репутацией 914, 22.09.2015
  • 60 рейтинг

    Это должно работать:

     SELECT name 
    FROM sysobjects 
    WHERE id IN ( SELECT id 
                  FROM syscolumns 
                  WHERE name like '%column_name%' )
     
    ответ дан cichy, с репутацией 8283, 31.01.2011
  • 59 рейтинг

    Если вы больше разбираетесь в сторонних инструментах, там много вариантов, таких как:

    Это очень удобно, если ваша база данных содержит зашифрованные объекты (представления, процедуры, функции), потому что их невозможно легко найти с помощью системных таблиц.

    ответ дан Dwoolk, с репутацией 1386, 14.04.2013
  • 46 рейтинг
     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%'
     

    Это даст вам немного дополнительной информации о схеме, таблицах и столбцах, в которых вы можете или не можете использовать дополнительные условия в своем предложении where для фильтрации. Например, если вы хотите видеть только поля, которые должны иметь значения, добавьте

     and c.is_nullable = 0
     

    Вы можете добавить другие условные обозначения, я также добавил столбцы в предложении select в этой вертикальной манере, поэтому было легко изменить порядок, удалить, переименовать или добавить другие в зависимости от ваших потребностей. В качестве альтернативы вы можете искать только таблицы, используя T.Name. Его очень настраиваемый.

    Наслаждаться.

    ответ дан Francis Rodgers, с репутацией 2268, 27.03.2013
  • 39 рейтинг

    Я не знаю, почему многие из вас предлагают присоединиться к sys.table with sys.columns вы можете просто использовать ниже код:

    Select object_name(object_id) as TableName,* from SYS.columns where name LIKE '%MyName%'

    или

    Если вы хотите также имя схемы:

     Select * from  INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME LIKE '%MyName%'
     
    ответ дан user3583912, с репутацией 772, 19.08.2014
  • 36 рейтинг

    Если вы просто хотите использовать имя таблицы, которое вы можете запустить:

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

    Если вы хотите также имя схемы (что во многих случаях вы будете, так как у вас будет много разных схем, и если вы не сможете запомнить каждую таблицу в базе данных и где она принадлежит, это может быть полезно) выполните:

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

    и, наконец, если вы хотите его в более удобном формате (хотя здесь код (по-моему) становится слишком сложным для легкого написания):

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

    обратите внимание, что вы также можете создать функцию, основанную на том, что у меня есть:

     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
     

    Стоит отметить, что функция concat была добавлена ​​в 2012 году. Для 2008r2 и более ранних используют + для конкатенации строк.

    Я немного отформатировал процесс, так как я разместил это. Сейчас он немного более продвинутый, но выглядит намного более грязным (но он в процессе, поэтому вы никогда его не увидите), и он отформатирован лучше.

    Эта версия позволяет вам иметь ее в административной базе данных, а затем осуществлять поиск через любую базу данных. Измените отклонение @db с 'master' на то, что вы хотите, чтобы база данных по умолчанию была (ПРИМЕЧАНИЕ. Использование функции CONCAT () будет работать только с 2012+, если вы не измените конкатенацию строк, чтобы использовать + операторов).

     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
     
    ответ дан Ste Bov, с репутацией 619, 27.08.2015
  • 24 рейтинг
     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; 
     

    Это от Pinal Sir Blog

    ответ дан shadab shah, с репутацией 420, 5.07.2012
  • 21 рейтинг

    Вы можете найти его из INFORMATION_SCHEMA.COLUMNS с помощью фильтра column_name

     Select DISTINCT TABLE_NAME as TableName,COLUMN_NAME as ColumnName
         From INFORMATION_SCHEMA.COLUMNS Where column_name like '%myname%'
     
    ответ дан Munavvar, с репутацией 596, 30.05.2016
  • 20 рейтинг
    SELECT  [TABLE_NAME] ,
            [INFORMATION_SCHEMA].COLUMNS.COLUMN_NAME
    FROM    INFORMATION_SCHEMA.COLUMNS
    WHERE   INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%NAME%' ;
    
    ответ дан Ritesh Varma-MCA, с репутацией 201, 18.10.2013
  • 18 рейтинг
    SELECT COLUMN_NAME, TABLE_NAME
      FROM INFORMATION_SCHEMA.COLUMNS    
     WHERE COLUMN_NAME LIKE '%myName%'
    
    ответ дан Neil Knight, с репутацией 35255, 31.01.2011
  • 15 рейтинг

    Следующий запрос даст вам точные имена таблиц базы данных с именем поля, например «% myName».

     SELECT distinct(TABLE_NAME)
      FROM INFORMATION_SCHEMA.COLUMNS    
     WHERE COLUMN_NAME LIKE '%myName%'
     
    ответ дан Shaikh Farooque, с репутацией 2228, 21.01.2013
  • 14 рейтинг

    Получить полную информацию: имя столбца, имя таблицы, а также схему таблицы.

     SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE '%col_Name%'
     
    ответ дан Kaleab, с репутацией 141, 1.08.2016
  • 12 рейтинг

    Я только что попробовал, и это отлично работает

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

    Только измените имя YourDatbaseName на свою базу данных и имя вашего столбца на свое имя столбца, которое вы ищете, оставите его как есть.

    Надеюсь, это помогло

    ответ дан Alz, с репутацией 206, 10.07.2014
  • 8 рейтинг
    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
    
    ответ дан Mauro Bilotti, с репутацией 2339, 14.03.2014
  • 7 рейтинг

    Я использовал это с той же целью, и это сработало:

       select * from INFORMATION_SCHEMA.COLUMNS
      where TABLE_CATALOG= 'theDatabase'
      and COLUMN_NAME like 'theCol%'
     
    ответ дан Rainhider, с репутацией 463, 23.06.2015
  • 6 рейтинг

    Надеюсь, это не дубликат ответа, но я хотел бы сгенерировать инструкцию sql в инструкции sql, которая позволит мне искать значения, которые я ищу (а не только таблицы с именами полей (как обычно мне необходимо удалить любую информацию, связанную с идентификатором имени столбца, которое я ищу):

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

    Затем я могу скопировать и вставить пробел 1-го столбца «SQLToRun» ... затем я заменю «Выбрать * из» на «Удалить из», и он позволяет мне удалять любые ссылки на этот идентификатор! Напишите эти результаты в файл, чтобы вы имейте их на всякий случай.

    ПРИМЕЧАНИЕ **** Убедитесь, что вы удалили все таблицы bakup перед запуском своего оператора delete ...

       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%'
     
    ответ дан Dan B, с репутацией 1260, 26.06.2014
  • 5 рейтинг
    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;
    
    ответ дан PedroSouki, с репутацией 641, 13.08.2015
  • 5 рейтинг

    Подобно оракулу, вы можете найти таблицы и столбцы с этим:

     select table_name, column_name
    from user_tab_columns 
    where column_name 
    like '%myname%';
     
    ответ дан Iceknight, с репутацией 58, 3.11.2015
  • 4 рейтинг

    Для Oracle, с обычными разрешениями пользователя:

     select owner, table_name, column_name
    from all_tab_columns 
    where column_name 
    like '%myname%';
     
    ответ дан edson-, с репутацией 113, 9.02.2017
  • 3 рейтинг

    Чтобы улучшить ответы выше, я включил Views, а также объединил схему и таблицу / просмотр, сделав результаты более очевидными.

     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];
     
    ответ дан Stephen Brett, с репутацией 41, 4.08.2015
  • 3 рейтинг

    Вы можете использовать таблицу [INFORMATION_SCHEMA].[COLUMNS] для поиска столбцов. Например,

     Select * From [INFORMATION_SCHEMA].[COLUMNS] Where COLUMN_NAME like '%Column%'
     

    Для получения информации о таблице и столбце для данного SQL-запроса посетите http://www.w3hattrick.com/2016/05/getting-table-and-column-information.html

    ответ дан pooranendu patel, с репутацией 39, 22.05.2016
  • 3 рейтинг

    Вы можете попробовать этот запрос:

     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%'
     
    ответ дан soheila sadeghian, с репутацией 49, 14.01.2017
  • 2 рейтинг
    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
    
    ответ дан Joby, с репутацией 508, 8.06.2016
  • 2 рейтинг
    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%'
    
    ответ дан Novice, с репутацией 440, 30.09.2016
  • 2 рейтинг
     select table_name, column_name
    from user_tab_columns where column_name like '%myname%';
     

    Оно работает

    ответ дан Pablo Glez, с репутацией 134, 7.10.2015
  • 0 рейтинг

    Вот рабочее решение для базы данных Sybase

     select 
      t.table_name, 
      c.column_name 
    from 
      systab as t key join systabcol as c 
    where 
       c.column_name = 'MyColumnName'
     
    ответ дан Janey, с репутацией 598, 4.07.2018
  • -2 рейтинг
    declare @ColumnName nvarchar(max)='QbId'
    
    Select b.Name as TableName from Sys.Columns a
    Join Sys.Tables b
    on a.Object_Id=b.Object_Id
    where a.Name=@ColumnName
    
    ответ дан UJS, с репутацией 533, 22.11.2016