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

Question

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

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

LIKE '%myName%'

?

1744

sql sql-server t-sql system-tables

Источник

user326821 31 янв '11 в 10:12

41 ответ

Другие вопросы по тегам sql sql-server t-sql system-tables

user135531 31 янв '11 в 10:17 2011-01-31 10:17 · Answer 1 · 2011-01-31 10:17

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

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;

user1001115 18 окт '11 в 12:45 2011-10-18 12:45 · Answer 2 · 2011-10-18 12:45

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

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

538

Источник

user1001115 18 окт '11 в 12:45

user5364810 22 сен '15 в 18:56 2015-09-22 18:56 · Answer 3 · 2015-09-22 18:56

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) Я использую его ВСЕ время, чтобы найти ВСЕ экземпляры имени столбца в заданной базе данных (схеме).

user246329 31 янв '11 в 10:17 2011-01-31 10:17 · Answer 4 · 2011-01-31 10:17

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

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

132

Источник

user246329 31 янв '11 в 10:17

user868278 27 мар '13 в 13:42 2013-03-27 13:42 · Answer 5 · 2013-03-27 13:42

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

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

and c.is_nullable = 0

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

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

user2107572 14 апр '13 в 01:06 2013-04-14 01:06 · Answer 6 · 2013-04-14 01:06

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

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

user3583912 19 авг '14 в 14:24 2014-08-19 14:24 · Answer 7 · 2014-08-19 14:24

Я не знаю, почему многие из вас предлагают присоединиться к 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%'

63

Источник

user3583912 19 авг '14 в 14:24

user4442467 27 авг '15 в 08:05 2015-08-27 08:05 · Answer 8 · 2015-08-27 08:05

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

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

user5567509 16 мар '21 в 03:20 2021-03-16 03:20 · Answer 9 · 2021-03-16 03:20

            SELECT   TABLE_SCHEMA +'.'+ TABLE_NAME    
      FROM [yourdatabase].INFORMATION_SCHEMA.COLUMNS
      WHERE COLUMN_NAME LIKE '%myName%'

Это даст вам имя таблицы столбца, который вам нужно найти.

37

Источник

user5567509 16 мар '21 в 03:20

user410636 31 янв '11 в 10:19 2011-01-31 10:19 · Answer 10 · 2011-01-31 10:19

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

32

Источник

user410636 31 янв '11 в 10:19

user8370127 04 мар '20 в 13:13 2020-03-04 13:13 · Answer 11 · 2020-03-04 13:13

Вот ответ на ваш вопрос

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

30

Источник

user8370127 04 мар '20 в 13:13

user1429149 05 июл '12 в 05:26 2012-07-05 05:26 · Answer 12 · 2012-07-05 05:26

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;

Это из блога Пинал сэр

29

Источник

user1429149 05 июл '12 в 05:26

user3261852 30 май '16 в 12:37 2016-05-30 12:37 · Answer 13 · 2016-05-30 12:37

Вы можете найти его в INFORMATION_SCHEMA.COLUMNS по фильтру column_name

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

26

Источник

user3261852 30 май '16 в 12:37

user1302958 21 янв '13 в 13:20 2013-01-21 13:20 · Answer 14 · 2013-01-21 13:20

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

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

24

Источник

user1302958 21 янв '13 в 13:20

user6308882 01 авг '16 в 13:30 2016-08-01 13:30 · Answer 15 · 2016-08-01 13:30

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

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

23

Источник

user6308882 01 авг '16 в 13:30

user2893556 18 окт '13 в 07:12 2013-10-18 07:12 · Answer 16 · 2013-10-18 07:12

SELECT  [TABLE_NAME] ,
        [INFORMATION_SCHEMA].COLUMNS.COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%NAME%' ;

22

Источник

user2893556 18 окт '13 в 07:12

user4197995 21 июл '20 в 15:26 2020-07-21 15:26 · Answer 17 · 2020-07-21 15:26

В базе данных MS SQL Server используйте этот запрос для получения имен таблиц и соответствующих столбцов, содержащих вводимый текст:

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

17

Источник

user4197995 21 июл '20 в 15:26

user5405967 03 мар '20 в 00:48 2020-03-03 00:48 · Answer 18 · 2020-03-03 00:48

Я хотел что-нибудь для таблиц и представлений, чтобы у меня не кровоточили глаза.

Запрос

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]

Результат

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

user8897909 08 сен '22 в 12:44 2022-09-08 12:44 · Answer 19 · 2022-09-08 12:44

Он вернет 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;

14

Источник

user8897909 08 сен '22 в 12:44

user3818892 10 июл '14 в 05:54 2014-07-10 05:54 · Answer 20 · 2014-07-10 05:54

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

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 только на свою базу данных, а YourcolumnName на имя столбца, которое вы ищете, а оставьте его как есть.

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

user1458328 17 авг '21 в 22:46 2021-08-17 22:46 · Answer 21 · 2021-08-17 22:46

Этот простой запрос сработал для меня.

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

9

Источник

user1458328 17 авг '21 в 22:46

user2936486 14 мар '14 в 14:21 2014-03-14 14:21 · Answer 22 · 2014-03-14 14:21

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

8

Источник

user2936486 14 мар '14 в 14:21

user7418035 14 янв '17 в 10:38 2017-01-14 10:38 · Answer 23 · 2017-01-14 10:38

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

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

7

Источник

user7418035 14 янв '17 в 10:38

user4166211 13 авг '15 в 13:28 2015-08-13 13:28 · Answer 24 · 2015-08-13 13:28

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;

7

Источник

user4166211 13 авг '15 в 13:28

user2208721 23 июн '15 в 14:17 2015-06-23 14:17 · Answer 25 · 2015-06-23 14:17

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

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

7

Источник

user2208721 23 июн '15 в 14:17

user623435 03 ноя '15 в 12:49 2015-11-03 12:49 · Answer 26 · 2015-11-03 12:49

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

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

7

Источник

user623435 03 ноя '15 в 12:49

user2440976 26 июн '14 в 21:51 2014-06-26 21:51 · Answer 27 · 2014-06-26 21:51

Надеюсь, это не повторяющийся ответ, но мне нравится генерировать 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%'

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

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

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

user5189234 04 авг '15 в 10:47 2015-08-04 10:47 · Answer 28 · 2015-08-04 10:47

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

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

user6304472 08 июн '16 в 07:47 2016-06-08 07:47 · Answer 29 · 2016-06-08 07:47

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

Mahum 21 июн '23 в 08:07 2023-06-21 08:07 · Answer 30 · 2023-06-21 08:07

Чтобы найти все таблицы, содержащие столбец с указанным именем в MS SQL Server, вы можете запросить представления системного каталога. В частности, вы можете запросить представления sys.tables и sys.columns. Вот пример запроса:

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

Заменятьcolumn_nameс именем столбца, который вы хотите найти. Этот запрос объединяет представления sys.tables и sys.columns на основе столбца object_id и фильтрует результат, чтобы получить имена таблиц, в которых имя столбца соответствует указанному значению.

В dbForge Studio для SQL Server вы можете использовать редактор SQL для выполнения упомянутого выше запроса. Он предлагает такие функции, как подсветка синтаксиса, завершение кода и просмотр результатов, что упрощает написание и эффективное выполнение SQL-запросов.