2 ответа
select table_name
from information_schema.columns
where column_name='Recorder_TYPE'
ответ дан 17 апр 2012 в 5:25
msimsi
11.4k15 серебряных знаков16 бронзовых знаков
select [Sch].[name] as [Schema], [Tab].[name] as [Table], [Col].[Name] as [Column]
from sys.tables as [Tab]
inner join sys.columns as [Col] on [Tab].[object_id] = [Col].[object_id]
inner join sys.schemas as [Sch] on [Tab].[schema_id] = [Sch].[schema_id]
where [Col].[Name] = 'Recorder_TYPE';
В предыдущем примере в результате запроса будут получены также «несуществующие» временные таблицы View.
ответ дан 24 мар 2016 в 3:02
AforAfor
312 бронзовых знака
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
Можно ли запросить имена таблиц, которые содержат столбцы
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%';
Я думаю, что это лучший ответ для вас. если есть вопросы, пожалуйста, спросите у меня.
Если вы просто хотите, чтобы имя таблицы можно было запустить:
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
Время на прочтение
5 мин
Количество просмотров 62K
Описание общей потребности в поиске данных и объектов в базе данных
Поиск данных, а также хранимых процедур, таблиц и других объектов в базе данных является достаточно актуальным вопросом в том числе и для C#-разработчиков, а также и для .NET-разработки в целом.
Достаточно часто может возникнуть ситуация, при которой нужно найти:
- объект базы данных (таблицу, представление, хранимую процедуру, функцию и т д)
- данные (значение и в какой таблице располагается)
- фрагмент кода в определениях объектов базы данных
Существует множество готовых решений как платных, так и бесплатных.
Сначала рассмотрим как можно осуществлять поиск данных и объектов в базе данных с помощью встроенных средств самой СУБД, а затем рассмотрим как это сделать с помощью бесплатной утилиты dbForge Search.
Поиск с помощью встроенных средств самой СУБД
Определить есть ли таблица Employee в базе данных можно с помощью следующего скрипта:
Поиск таблицы по имени
select [object_id], [schema_id],
schema_name([schema_id]) as [schema_name],
[name],
[type],
[type_desc],
[create_date],
[modify_date]
from sys.all_objects
where [name]='Employee';
Результат может быть примерно такой:
Здесь выводятся:
- идентификаторы объекта и схемы, где располагается объект
- название этой схемы и название этого объекта
- тип объекта и описание этого типа объекта
- даты и время создания и последней модификации объекта
Чтобы найти все вхождения строки “Project”, то можно использовать следующий скрипт:
Поиск всех объектов по подстроке в имени
select [object_id], [schema_id],
schema_name([schema_id]) as [schema_name],
[name],
[type],
[type_desc],
[create_date],
[modify_date]
from sys.all_objects
where [name] like '%Project%';
Результат может быть примерно такой:
Как видно из результата, здесь подстроку “Project” содержат не только две таблицы Project и ProjectSkill, но и также некоторые первичные и внешние ключи.
Чтобы понять кому именно принадлежат данные ключи, добавим в вывод поле parent_object_id и его имя и схему, в которой он располагается следующим образом:
Поиск всех объектов по подстроке в имени с выводом родительских объектов
select ao.[object_id], ao.[schema_id],
schema_name(ao.[schema_id]) as [schema_name],
ao.parent_object_id,
p.[schema_id] as [parent_schema_id],
schema_name(p.[schema_id]) as [parent_schema_name],
p.[name] as [parent_name],
ao.[name],
ao.[type],
ao.[type_desc],
ao.[create_date],
ao.[modify_date]
from sys.all_objects as ao
left outer join sys.all_objects as p on ao.[parent_object_id]=p.[object_id]
where ao.[name] like '%Project%';
Результатом будет вывод таблицы с детальной информацией о родительских объектах, т е где определены первичные и внешние ключи:
В запросах используются следующие системные объекты:
- таблица sys.all_objects
- скалярная функция schema_name
Итак, разобрали как найти объекты в базе данных с помощью встроенных средств самой СУБД.
Теперь покажем как найти данные в базе данных на примере поиска строк.
Чтобы найти строковое значение по всем таблицам базы данных, можно воспользоваться следующим решением. Упростим данное решение и покажем как можно найти например значение “Ramiro” с помощью следующего скрипта:
Поиск строковых значений по подстроке во всех таблицах базы данных
set nocount on
declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
set @substr = '%Ramiro%'
declare @sql nvarchar(max);
create table #rslt
(table_name varchar(128), field_name varchar(128), [value] nvarchar(max))
declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
open s
fetch next from s into @name
while @@fetch_status = 0
begin
declare c cursor for
select quotename(column_name) as column_name from information_schema.columns
where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname', 'int', 'tinyint') and table_name = @name
set @name = quotename(@name)
open c
fetch next from c into @column
while @@fetch_status = 0
begin
--print 'Processing table - ' + @name + ', column - ' + @column
set @sql='insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', cast(' + @column +
' as nvarchar(max)) from' + @name + ' where cast(' + @column + ' as nvarchar(max)) like ''' + @substr + '''';
print @sql;
exec(@sql);
fetch next from c into @column;
end
close c
deallocate c
fetch next from s into @name
end
select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
group by table_name, field_name
order by table_name, field_name
drop table #rslt
close s
deallocate s
Результат выполнения может быть таким:
Здесь выводятся имена таблиц и в каких столбцах хранится значение, содержащие подстроку “Ramiro”. А также количество найденных входов данной подстроки для найденной пары таблица-колонка.
Чтобы найти объекты, в определениях которых есть заданный фрагмент кода, можно воспользоваться следующими системными представлениями:
- sys.sql_modules
- sys.all_sql_modules
- sys.syscomments
Например, используя последнее представление, можно с помощью следующего скрипта найти все объекты, в определениях которых встречается заданный фрагмент кода:
Поиск фрагмента кода в определениях объектов базы данных
select obj.[object_id],
obj.[name],
obj.[type_desc],
sc.[text]
from sys.syscomments as sc
inner join sys.objects obj on sc.[id]=obj.[object_id]
where sc.[text] like '%code snippet%';
Здесь будет выведен идентификатор, название, описание и полное определение объекта.
Поиск с помощью бесплатной утилиты dbForge Search
Однако, более удобно поиск производить с помощью готовых хороших инструментов. Одним из таких инструментов является dbForge Search.
Для вызова этой утилиты в окне SSMS нажмите на кнопку .
Появится следующее окно поиска:
Обратите внимание на верхнюю панель (слева направо):
- можно переключать режим поиска (ищем DDL (объекты) или данные)
- непосредственно что ищем (какую подстроку)
- учитывать ли регистр, искать точное соответствие слову, искать вхождения:
- группировать результат по типам объектов — кнопка
- выбрать нужные типы объектов для поиска:
- также можно задать несколько баз данных для поиска и выбрать экземпляр MS SQL Server
Это все в режиме поиска объектов, т е когда включен DDL:
В режиме поиска данных изменится только выбор типов объектов:
А именно будут доступны для выбора только таблицы, где и хранятся собственно сами данные:
Теперь как и раньше найдем все вхождения подстроки “Project” в названиях объектов:
Как видно, был выбран режим поиска по DDL-объектам, заполнено что ищем-строка “Project”, остальное все было по умолчанию.
При выделении найденного объекта внизу отображается код определения данного объекта или всего его родительского объекта.
Также можно переместить навигацию на найденный объект, щелкнув на кнопку :
Можно также сгруппировать найденные объекты по их типу:
Обратите внимание, что выводятся даже те таблицы, в которых есть поля, в именах которых содержится подстрока “Project”. Однако, напомним, что режим поиска можно менять: искать полное соответствие/частичное/учитывать регистр или нет.
Теперь найдем значение “Ramiro” по всем таблицам:
Обратите внимание, что внизу отображаются все строки, в которых содержится подстрока “Ramiro” выбранной таблицы Employee.
Также можно переместить навигацию к найденному объекту, нажав как и ранее на кнопку :
Таким образом мы можем искать нужные объекты и данные в базе данных.
Заключение
Были рассмотрены способы поиска как самих данных, так и объектов в базе данных как с помощью встроенных средств самой СУБД MS SQL Server, так и с помощью бесплатной утилиты dbForge Search.
Также от компании Devart есть и ряд других бесплатных готовых решений, полный список которых можно посмотреть здесь.
Источники
- Search_Script.sql
- SSMS
- dbForge Search
- Документация по Microsoft SQL
- Бесплатные решения от компании Devart