To all: Thanks for all the post and comments some are good, but some are better.
The first big script is good because it is delivers just what is needed. The fastest and most detailed is the one suggestion for selecting from INFORMATION_SCHEMA.COLUMNS..
My need was to find all the errant columns of approximately the same name and Several databases.. Sooo, I made my versions of both (see below) …Either of these two below script work and deliver the goods in seconds.
The assumption in other posts on this link, is that the first code example can be used successfully with for-each-database, is to me, not desirable. This is because the information is within the specific database and the simple use of the «fedb» doesn’t produce the correct results, it simply doesn’t give access. SOOO to that is why I use a CURSOR to collect the databases and ignore those that are Off-line, which in this case, a utility script, it is a good use of same.
Bottom Line, I read everyone’s post, incorporated all the correction from the posts and made what are two very eloquent scripts from others good works. I listed both below and have also placed the script file on my public folder at OneDrive.com which you can access with this link: http://1drv.ms/1vr8yNX
Enjoy !
Hank Freeman
Senior Level — SQL Server DBA — Data Architect
Try them separately…
---------------------------
--- 1st example (works) ---
---------------------------
Declare
@DBName sysname
,@SQL_String1 nvarchar(4000)
,@SQL_String2 nvarchar(4000)
,@ColumnName nvarchar(200)
--set @ColumnName = 'Course_ID'
-------- Like Trick --------
-- IF you want to add more the @ColumnName so it looks like Course_ID,CourseID
-- then add an additional pairing of +''','''+'NewColumnSearchIDValue'
----------------------------
set @ColumnName = 'Course_ID' +''','''+'CourseID'
--select @ColumnName
-----
Declare @Column_Info table
(
[DatabaseName] nvarchar(128) NULL,
[ColumnName] sysname NULL,
[ObjectName] nvarchar(257) NOT NULL,
[ObjectType] nvarchar(60) NULL,
[DataType] nvarchar(151) NULL,
[Nullable] varchar(8) NOT NULL,
[MiscInfo] nvarchar(MAX) NOT NULL
)
--------------
Begin
set @SQL_String2 = 'SELECT
DB_NAME() as ''DatabaseName'',
s.name as ColumnName
,sh.name+''.''+o.name AS ObjectName
,o.type_desc AS ObjectType
,CASE
WHEN t.name IN (''char'',''varchar'') THEN t.name+''(''+CASE WHEN s.max_length<0 then ''MAX'' ELSE CONVERT(varchar(10),s.max_length) END+'')''
WHEN t.name IN (''nvarchar'',''nchar'') THEN t.name+''(''+CASE WHEN s.max_length<0 then ''MAX'' ELSE CONVERT(varchar(10),s.max_length/2) END+'')''
WHEN t.name IN (''numeric'') THEN t.name+''(''+CONVERT(varchar(10),s.precision)+'',''+CONVERT(varchar(10),s.scale)+'')''
ELSE t.name
END AS DataType
,CASE
WHEN s.is_nullable=1 THEN ''NULL''
ELSE ''NOT NULL''
END AS Nullable
,CASE
WHEN ic.column_id IS NULL THEN ''''
ELSE '' identity(''+ISNULL(CONVERT(varchar(10),ic.seed_value),'''')+'',''+ISNULL(CONVERT(varchar(10),ic.increment_value),'''')+'')=''+ISNULL(CONVERT(varchar(10),ic.last_value),''null'')
END
+CASE
WHEN sc.column_id IS NULL THEN ''''
ELSE '' computed(''+ISNULL(sc.definition,'''')+'')''
END
+CASE
WHEN cc.object_id IS NULL THEN ''''
ELSE '' check(''+ISNULL(cc.definition,'''')+'')''
END
AS MiscInfo
into ##Temp_Column_Info
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
LEFT OUTER JOIN sys.identity_columns ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
LEFT OUTER JOIN sys.computed_columns sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
--------------------------------------------
--- DBA - Hank 12-Feb-2015 added this specific where statement
-- where Upper(s.name) like ''COURSE%''
-- where Upper(s.name) in (''' + @ColumnName + ''')
-- where Upper(s.name) in (''cycle_Code'')
-- ORDER BY sh.name+''.''+o.name,s.column_id
order by 1,2'
--------------------
Declare DB_cursor CURSOR
FOR
SELECT name FROM sys.databases
--select * from sys.databases
WHERE STATE = 0
-- and Name not IN ('master','msdb','tempdb','model','DocxPress')
and Name not IN ('msdb','tempdb','model','DocxPress')
Open DB_cursor
Fetch next from DB_cursor into @DBName
While @@FETCH_STATUS = 0
begin
--select @DBName as '@DBName';
Set @SQL_String1 = 'USE [' + @DBName + ']'
set @SQL_String1 = @SQL_String1 + @SQL_String2
EXEC sp_executesql @SQL_String1;
--
insert into @Column_Info
select * from ##Temp_Column_Info;
drop table ##Temp_Column_Info;
Fetch next From DB_cursor into @DBName
end
CLOSE DB_cursor;
Deallocate DB_cursor;
---
select * from @Column_Info order by 2,3
----------------------------
end
---------------------------
Below is the Second script..
---------------------------
--- 2nd example (works) ---
---------------------------
-- This is by far the best/fastes of the lot for what it delivers.
--Select * into dbo.hanktst From Master.INFORMATION_SCHEMA.COLUMNS
--FileID: SCRIPT_Get_Column_info_(INFORMATION_SCHEMA.COLUMNS).sql
----------------------------------------
--FileID: SCRIPT_Get_Column_info_(INFORMATION_SCHEMA.COLUMNS).sql
-- Utility to find all columns in all databases or find specific with a like statement
-- Look at this line to find a: --> set @SQL_String2 = ' select * into ##Temp_Column_Info....
----------------------------------------
---
SET NOCOUNT ON
begin
Declare @hanktst TABLE (
[TABLE_CATALOG] NVARCHAR(128) NULL
,[TABLE_SCHEMA] NVARCHAR(128) NULL
,[TABLE_NAME] sysname NOT NULL
,[COLUMN_NAME] sysname NULL
,[ORDINAL_POSITION] INT NULL
,[COLUMN_DEFAULT] NVARCHAR(4000) NULL
,[IS_NULLABLE] VARCHAR(3) NULL
,[DATA_TYPE] NVARCHAR(128) NULL
,[CHARACTER_MAXIMUM_LENGTH] INT NULL
,[CHARACTER_OCTET_LENGTH] INT NULL
,[NUMERIC_PRECISION] TINYINT NULL
,[NUMERIC_PRECISION_RADIX] SMALLINT NULL
,[NUMERIC_SCALE] INT NULL
,[DATETIME_PRECISION] SMALLINT NULL
,[CHARACTER_SET_CATALOG] sysname NULL
,[CHARACTER_SET_SCHEMA] sysname NULL
,[CHARACTER_SET_NAME] sysname NULL
,[COLLATION_CATALOG] sysname NULL
,[COLLATION_SCHEMA] sysname NULL
,[COLLATION_NAME] sysname NULL
,[DOMAIN_CATALOG] sysname NULL
,[DOMAIN_SCHEMA] sysname NULL
,[DOMAIN_NAME] sysname NULL
)
Declare
@DBName sysname
,@SQL_String2 nvarchar(4000)
,@TempRowCnt varchar(20)
,@Dbug bit = 0
Declare DB_cursor CURSOR
FOR
SELECT name FROM sys.databases
WHERE STATE = 0
-- and Name not IN ('master','msdb','tempdb','model','DocxPress')
and Name not IN ('msdb','tempdb','model','DocxPress')
Open DB_cursor
Fetch next from DB_cursor into @DBName
While @@FETCH_STATUS = 0
begin
set @SQL_String2 = ' select * into ##Temp_Column_Info from [' + @DBName + '].INFORMATION_SCHEMA.COLUMNS
where UPPER(Column_Name) like ''COURSE%''
;'
if @Dbug = 1 Select @SQL_String2 as '@SQL_String2';
EXEC sp_executesql @SQL_String2;
insert into @hanktst
select * from ##Temp_Column_Info;
drop table ##Temp_Column_Info;
Fetch next From DB_cursor into @DBName
end
select * from @hanktst order by 4,2,3
CLOSE DB_cursor;
Deallocate DB_cursor;
set @TempRowCnt = (select cast(count(1) as varchar(10)) from @hanktst )
Print ('Rows found: '+ @TempRowCnt +' end ...')
end
--------
If you are avoiding stored procedures
like the plague, or are unable to do a mysql_dump
due to permissions, or running into other various reasons.
I would suggest a three-step approach like this:
1) Where this query builds a bunch of queries as a result set.
# =================
# VAR/CHAR SEARCH
# =================
# BE ADVISED USE ANY OF THESE WITH CAUTION
# DON'T RUN ON YOUR PRODUCTION SERVER
# ** USE AN ALTERNATE BACKUP **
SELECT
CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME,
' WHERE ', A.COLUMN_NAME, ' LIKE '%stuff%';')
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE
A.TABLE_SCHEMA != 'mysql'
AND A.TABLE_SCHEMA != 'innodb'
AND A.TABLE_SCHEMA != 'performance_schema'
AND A.TABLE_SCHEMA != 'information_schema'
AND
(
A.DATA_TYPE LIKE '%text%'
OR
A.DATA_TYPE LIKE '%char%'
)
;
.
# =================
# NUMBER SEARCH
# =================
# BE ADVISED USE WITH CAUTION
SELECT
CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME,
' WHERE ', A.COLUMN_NAME, ' IN ('%1234567890%');')
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE
A.TABLE_SCHEMA != 'mysql'
AND A.TABLE_SCHEMA != 'innodb'
AND A.TABLE_SCHEMA != 'performance_schema'
AND A.TABLE_SCHEMA != 'information_schema'
AND A.DATA_TYPE IN ('bigint','int','smallint','tinyint','decimal','double')
;
.
# =================
# BLOB SEARCH
# =================
# BE ADVISED THIS IS CAN END HORRIFICALLY IF YOU DONT KNOW WHAT YOU ARE DOING
# YOU SHOULD KNOW IF YOU HAVE FULL TEXT INDEX ON OR NOT
# MISUSE AND YOU COULD CRASH A LARGE SERVER
SELECT
CONCAT('SELECT CONVERT(',A.COLUMN_NAME, ' USING utf8) FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME,
' WHERE CONVERT(',A.COLUMN_NAME, ' USING utf8) IN ('%someText%');')
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE
A.TABLE_SCHEMA != 'mysql'
AND A.TABLE_SCHEMA != 'innodb'
AND A.TABLE_SCHEMA != 'performance_schema'
AND A.TABLE_SCHEMA != 'information_schema'
AND A.DATA_TYPE LIKE '%blob%'
;
Results should look like this:
2) You can then just Right Click
and use the Copy Row (tab-separated)
3) Paste results in a new query window and run to your heart’s content.
Detail: I exclude system schema’s that you may not usually see in your workbench unless you have the option Show Metadata and Internal Schemas
checked.
I did this to provide a quick way to ANALYZE
an entire HOST or DB if needed or to run OPTIMIZE
statements to support performance improvements.
I’m sure there are different ways you may go about doing this but here’s what works for me:
-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO ANALYZE THEM
SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';
-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO OPTIMIZE THEM
SELECT CONCAT('OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';
Tested On MySQL Version: 5.6.23
WARNING: DO NOT RUN THIS IF:
- You are concerned with causing Table-locks (keep an eye on your client-connections)
You are unsure about what you are doing.
You are trying to anger you DBA. (you may have people at your desk with the quickness.)
Cheers, Jay ;-]
Есть несколько способов произвести поиск по БД, когда мы не понимаем где нам нужно искать:
- Подумать как могут называться нужные нам таблицы и произвести поиск по названиям таблиц, после чего поискать там
- Поиска можно не только по таблицам, но и по колонкам
- Два предыдущих способа не дают 100% результата, поэтому предлагаю вам рассмотреть ещё один. Этот способ даёт больше шансов найти то что мы ищем, но он значительно дольше и тратит ресурсы нашего сервера.
- Можно воспользоваться плагином для SSMS — ApexSQL Search
Рассмотрим 3-ий способ подробнее. Суть заключается в том, чтобы произвести поиск по всем таблицам и колонкам в Базе Данных. Чтобы скриптом было удобно пользоваться, мы обернём его в процедуру SearchAllTables, которая ищет по всем колонкам типа char, varchar, nchar, nvarchar по всей БД (системные таблицы исключены из поиска)
Вывод этой процедуры состоит из 2х колонок:
- Имя таблица и колонки, в которой были найдены совпадения
- Текст, в котором были найдены совпадения (будут отображены только первые 3630 символов)
Будьте осторожны, на больших Базах Данных скрипт может работать очень долго (часы). Так что запуская его на больших БД, будьте готовы ждать.
Создайте процедуру в той БД, в которой необходимо произвести поиск:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = » SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,»») WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = » SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) ), ‘IsMSShipped’ ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’) AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( ‘SELECT ‘» + @TableName + ‘.’ + @ColumnName + »‘, LEFT(‘ + @ColumnName + ‘, 3630) FROM ‘ + @TableName + ‘ (NOLOCK) ‘ + ‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results END |
Вызов процедуры выглядит так:
EXEC SearchAllTables ‘Computer’ GO |
Так же есть очень похожий вариант этого скрипта через другие представления. На этот раз мы обойдёмся без процедур. Строку поиска необходимо указать в @SearchText :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
DECLARE @SearchText varchar(200), @Table varchar(100), @TableID int, @ColumnName varchar(100), @String varchar(1000); —modify the variable, specify the text to search for SET @SearchText = ‘John’; DECLARE CursorSearch CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = ‘U’; —list of tables in the current database. Type = ‘U’ = tables(user-defined) OPEN CursorSearch; FETCH NEXT FROM CursorSearch INTO @Table, @TableID; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE CursorColumns CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @TableID AND system_type_id IN(167, 175, 231, 239); — the columns that can contain textual data —167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar OPEN CursorColumns; FETCH NEXT FROM CursorColumns INTO @ColumnName; WHILE @@FETCH_STATUS = 0 BEGIN SET @String = ‘IF EXISTS (SELECT * FROM ‘ + @Table + ‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘‘%’ + @SearchText + ‘%’‘) PRINT ‘» + @Table + ‘, ‘ + @ColumnName + »»; EXECUTE (@String); FETCH NEXT FROM CursorColumns INTO @ColumnName; END; CLOSE CursorColumns; DEALLOCATE CursorColumns; FETCH NEXT FROM CursorSearch INTO @Table, @TableID; END; CLOSE CursorSearch; DEALLOCATE CursorSearch; |
Интересная, но более сложная, реализация поиска (проверено только на SQL Server 2014)
Вырезка из статей:
- How to search all columns of all tables in a database for a keyword?
- How to quickly search for SQL database data and objects
Запись опубликована в рубрике Полезно и интересно с метками t-sql. Добавьте в закладки постоянную ссылку.
Можно ли запросить имена таблиц, которые содержат столбцы
LIKE '%myName%'
?
Ответ 1
Таблицы поиска:
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;
Ответ 2
Мы также можем использовать следующий синтаксис: —
select * from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like '%clientid%'
order by TABLE_NAME
Ответ 3
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)
Я использую это ВСЕ время, чтобы найти ВСЕ экземпляры имени столбца в заданной базе данных (схеме).
Ответ 4
Это должно работать:
SELECT name
FROM sysobjects
WHERE id IN ( SELECT id
FROM syscolumns
WHERE name like '%column_name%' )
Ответ 5
Если вы больше используете сторонние инструменты, там есть много вариантов, таких как:
- ApexSQL Search
- SSMS Toolpack
- Инструменты Red Gate.
Это очень удобно, если ваша база данных содержит зашифрованные объекты (представления, процедуры, функции), потому что вы не можете легко найти их с помощью системных таблиц.
Ответ 6
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%'
Здесь вы получите немного дополнительной информации о схеме, таблицах и столбцах, которые вы можете или не можете использовать для фильтрации в дополнительных условиях. Например, если вы хотите видеть только те поля, которые должны иметь значения, добавьте
and c.is_nullable = 0
Вы можете добавить другие условия, я также добавил столбцы в предложении select по вертикали, чтобы было легко переупорядочивать, удалять, переименовывать или добавлять другие в зависимости от ваших потребностей. С другой стороны, вы можете искать только таблицы с помощью T.Name. Его очень настраиваемый.
Наслаждаться.
Ответ 7
Я не знаю, почему многие из вас предлагают присоединиться к 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%'
Ответ 8
Если вы просто хотите, чтобы имя таблицы можно было запустить:
select object_name(object_id) from sys.columns
where name like '%received_at%'
Если вы хотите также имя схемы (что во многих случаях вы будете иметь, так как у вас будет много разных схем, и если вы не сможете запомнить каждую таблицу в базе данных и где она принадлежит, это может быть полезно ) run:
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 и более ранних версий используйте + для конкатенации строк.
Я немного переформатировал proc, так как я разместил это. Он немного более продвинутый, но выглядит намного более грязным (но это в proc, поэтому вы никогда его не увидите), и он отформатирован лучше.
Эта версия позволяет вам иметь ее в административной базе данных, а затем искать через любую базу данных. Измените отклонение @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
Ответ 9
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
Ответ 10
Вы можете найти его из INFORMATION_SCHEMA.COLUMNS с помощью фильтра column_name
Select DISTINCT TABLE_NAME as TableName,COLUMN_NAME as ColumnName
From INFORMATION_SCHEMA.COLUMNS Where column_name like '%myname%'
Ответ 11
SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%'
Ответ 12
SELECT [TABLE_NAME] ,
[INFORMATION_SCHEMA].COLUMNS.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%NAME%' ;
Ответ 13
Следующий запрос даст вам точные имена таблиц базы данных с именем поля, например ‘% myName’.
SELECT distinct(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%'
Ответ 14
Получить полную информацию: имя столбца, имя таблицы, а также схему таблицы.
SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%col_Name%'
Ответ 15
Я только что попробовал, и это отлично работает
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 на свою базу данных и имя вашего столбца на имя столбца, которое вы ищете, оставите его как есть.
Надеюсь, это помогло
Ответ 16
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
Ответ 17
Я использовал это для той же цели, и он работал:
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG= 'theDatabase'
and COLUMN_NAME like 'theCol%'
Ответ 18
Надеюсь, это не дубликат ответа, но я хотел бы сгенерировать инструкцию 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%'
Ответ 19
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;
Ответ 20
Как и оракул, вы можете найти таблицы и столбцы с этим:
select table_name, column_name
from user_tab_columns
where column_name
like '%myname%';
Ответ 21
Для Oracle, с обычными разрешениями пользователя:
select owner, table_name, column_name
from all_tab_columns
where column_name
like '%myname%';
Ответ 22
Чтобы улучшить ответы выше, я включил 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];
Ответ 23
Вы можете использовать таблицу [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
Ответ 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 '%myName%'
Ответ 25
select table_name, column_name
from user_tab_columns where column_name like '%myname%';
Работает
Ответ 26
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
Ответ 27
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%'
Ответ 28
Вот рабочее решение для базы данных Sybase
select
t.table_name,
c.column_name
from
systab as t key join systabcol as c
where
c.column_name = 'MyColumnName'
Ответ 29
Поиск по именам столбцов базы данных SQL возможен с помощью SQL Server Management Studio следующими способами, с помощью поиска объектов SSMS: подробности об объектном обозревателе или сценарии T-SQL, как описано ниже:
- Различные способы поиска объектов базы данных sql server
- SQL Server находит что-либо в Object Explorer в SSMS
- Поиск текста с подстановочными знаками
Ответ 30
Вот решение, которое я нашел.
SELECT C.NAME Column_Name, T.NAME Table_Name
FROM SYS.COLUMNS C INNER JOIN
SYS.TABLES T ON C.OBJECT_ID = T.OBJECT_ID
WHERE C.NAME LIKE '%idTipoMotivo%';
Я думаю, что это лучший ответ для вас. если есть вопросы, пожалуйста, спросите у меня.
Андрей Васенин
Автор статьи. Сфера интересов: ИТ-специалист (программирование, администрирование, DBA). Кандидат экономических наук. Подробнее .
Иногда программисту требуется найти в базе данных MS SQL найти столбец или несколько колонок по ключевому слову / названию / вхождению символов. Сделать это довольно просто. Вот пример кода такого запроса:
USE dbname
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 ‘%something%’
ORDER BY schema_name, table_name;
На выходи мы получим все столбы всех таблиц базы данных, в именах которых есть вхождение строки «something»