Время на прочтение
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
There are several ways to search and find the tables in the SQL Server database by table name or by column name. Below are few of the methods to find table in the database. I’ve used these methods in SQL Server 2014 and they are working good.
I. Find Table By Table Name Querying sys.tables
The most common and simple method to find and list down the tables in a database based on the name of the table or a phrase is by using this simple select query against the system table sys.tables. If you are a sql expert then this will be the first option you will choose.
SELECT * FROM sys.tables WHERE name LIKE '%product%'
II. Find Table By Table Name Using Filter Settings in Object Explores
Another easiest method to find the tables by the table’s name in SQL Server database is to use the filter settings option in the object explorer in SQL Server Management Studio.
- In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
- Right Click the Tables folder and select Filter in the right-click menu.
- Under filter, select Filter Settings. The filter settings window will pop up.
- In the filter settings window against the property Name, you can choose one of the Operator from the list (Equals, Contains, Does not contain) and enter the search phrase (or table name) under Value and then click OK.
- The list of tables under the Tables folder are filtered based on your filter criteria.
III. Find Table From All The Databases By Table Name
There could be a specific scenario to find the table with the same name or phrase of the name from all the databases in the SQL Server. If the SQL Server has less than 10 user databases then it will be easy for you to use one among the above two methods to find the table in the databases separately. But, In case if the SQL Server has hundreds of databases, then searching for the table in each database is difficult and time-consuming. In this scenario you can use the sp_msforeachdb system stored procedure. sp_msforeachdb allows you to execute a command against all the databases in the current SQL Server. You can use this system stored procedure to execute a select command to fetch the list of tables with filter condition from sys.tables against all the databases.
EXEC sys.sp_msforeachdb 'SELECT ''?'' DatabaseName, Name FROM [?].sys.Tables WHERE Name LIKE ''%product%'''
IV. Find Table By Column Name Using SQL Query
Another common scenario in the database world is to find the tables having the specific column or the columns containing a phrase in the name. For such a need to find the tables having the column, you can query the sys.tables system table and joining the sys.columns system table. Here is the simple select query to find the list of tables having a column.
SELECT sys.tables.name AS 'Table Name', sys.tables.object_id AS 'Object ID', sys.columns.name AS 'Column Name' FROM sys.tables INNER JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id WHERE sys.columns.name LIKE '%ProductID%' ORDER BY 1;
Another method to get the list of tables having the same column is by querying the sys.columns table along with the OBJECT_NAME built-in meta data function instead of using sys.column table. Here is the select command.
SELECT OBJECT_NAME(object_id) AS 'Table Name', object_id, name AS 'Column Name' FROM sys.columns WHERE name LIKE '%ProductID%'
If you know any other method please do add them by commenting below.
Related Articles
- Get the row count of all the tables In a database.
- Search For Stored Procedure.
- Get the list of all the user defined functions in a database.
Reference
- Stackoverflow
Find a Table on a SQL Server across all Databases
To find a table in the SQL across all databases you can use undocumented stored procedure
sp_MSForEachDB.
sp_MSforeachdb
'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like '
'%your_table_name%'
''
Alternatively, instead of using undocumented stored procedure, you can simply create a query like this:
DECLARE
@SQL NVARCHAR(
max
)
SET
@SQL = stuff((
SELECT
'
UNION
SELECT '
+ quotename(
NAME
,
''
''
) +
' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name
FROM '
+ quotename(
NAME
) +
'.sys.tables WHERE NAME LIKE '
'%'
' + @TableName + '
'%'
''
FROM
sys.databases
ORDER
BY
NAME
FOR
XML PATH(
''
)
,type
).value(
'.'
,
'nvarchar(max)'
), 1, 8,
''
)
--PRINT @SQL;
EXECUTE
sp_executeSQL @SQL
,N
'@TableName varchar(30)'
,@TableName =
'items'
Other Languages
- Encontrando uma Tabela sobre Todos
os Bancos de Dados SQL Server (pt-BR)
en-US, has code, has code block, has image, has Other Languages, Has Table, Multi Language Wiki Articles, search a table, SQL Server, Translated into Portuguese, T-SQL
Comments
-
22 Nov 2013 10:16 PM
Nice script !
-
7 Apr 2014 1:22 PM
I’ve changed the alternative query to display the actual table name instead of the search string. Apparently my ReportServer databases have a different collation so I had to add the collate part as well.
-
19 Feb 2015 1:46 AM
It might be good to add where statement to limit the result to databases where state is «online»
WHERE state = 0
msdn.microsoft.com/…/ms178534.aspx
-
16 Oct 2017 3:28 AM
For using the undocumented SP and returning only results if the table is found you can use the following:
sp_MSforeachdb ‘if exists (SELECT «?» AS DB, * FROM [?].sys.tables WHERE name like »%MSP_EpmProject%») SELECT «?» AS DB, * FROM [?].sys.tables WHERE name like »%MSP_EpmProject%»’
-
3 Apr 2020 6:40 PM
Nice to have this clause
WHERE user_access_desc =’MULTI_USER’ and state_desc = ‘ONLINE’ AND is_read_only = 0
-
3 Apr 2020 6:59 PM
Nice to have this clause
WHERE user_access_desc =’MULTI_USER’ and state_desc = ‘ONLINE’ AND is_read_only = 0 AND database_id > 4
I use the following to search for strings within the stored procedures of a specific database:
USE DBname
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%xxx%'
Is it easy to amend the above so that it searches Table names in a specific db «DBname» ?
asked Oct 26, 2012 at 11:00
whytheqwhytheq
34.1k64 gold badges170 silver badges265 bronze badges
1
I’m using this and works fine
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%%'
answered Mar 3, 2014 at 14:03
NeshaSerbiaNeshaSerbia
2,2842 gold badges14 silver badges13 bronze badges
5
select name
from DBname.sys.tables
where name like '%xxx%'
and is_ms_shipped = 0; -- << comment out if you really want to see them
answered Oct 26, 2012 at 11:03
RichardTheKiwiRichardTheKiwi
105k26 gold badges196 silver badges262 bronze badges
3
If you want to look in all tables in all Databases server-wide and get output you can make use of the undocumented sp_MSforeachdb procedure:
sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%Table_Names%'''
Matthew
1,6101 gold badge13 silver badges17 bronze badges
answered Dec 30, 2017 at 18:06
ransemsransems
6317 silver badges19 bronze badges
1
You can also use the Filter button to filter tables with a certain string in it.
You can do the same with stored procedures and views.
answered Mar 21, 2019 at 17:04
live-lovelive-love
47.7k22 gold badges234 silver badges201 bronze badges
I am assuming you want to pass the database name as a parameter and not just run:
SELECT *
FROM DBName.sys.tables
WHERE Name LIKE '%XXX%'
If so, you could use dynamic SQL to add the dbname to the query:
DECLARE @DBName NVARCHAR(200) = 'YourDBName',
@TableName NVARCHAR(200) = 'SomeString';
IF NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE Name = @DBName)
BEGIN
PRINT 'DATABASE NOT FOUND';
RETURN;
END;
DECLARE @SQL NVARCHAR(MAX) = ' SELECT Name
FROM ' + QUOTENAME(@DBName) + '.sys.tables
WHERE Name LIKE ''%'' + @Table + ''%''';
EXECUTE SP_EXECUTESQL @SQL, N'@Table NVARCHAR(200)', @TableName;
answered Oct 26, 2012 at 11:07
GarethDGarethD
67.7k10 gold badges83 silver badges122 bronze badges
If you prefer case-insensitive searching:
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME ILIKE '%%'
or
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE Lower(TABLE_NAME) LIKE Lower('%%')
answered Nov 29, 2018 at 11:25
Tim StackTim Stack
3,2093 gold badges18 silver badges39 bronze badges
Adding on to @[RichardTheKiwi]’s answer.
Whenever I search for a list of tables, in general I want to select from all of them or delete them. Below is a script that generates those scripts for you.
The generated select script also adds a tableName column so you know what table you’re looking at:
select 'select ''' + name + ''' as TableName, * from ' + name as SelectTable,
'delete from ' + name as DeleteTable
from sys.tables
where name like '%xxxx%'
and is_ms_shipped = 0;
answered Mar 29, 2016 at 9:07
RafiRafi
2,4131 gold badge25 silver badges32 bronze badges
you can also use the show command.
show tables like '%tableName%'
answered Feb 8, 2017 at 13:22
1
I want to post a simple solution for every schema you’ve got. If you are using MySQL DB, you can simply get from your schema all the table’s name and add the WHERE-LIKE condition on it. You also could do it with the usual command line as follows:
SHOW TABLES WHERE tables_in_<your_shcema_name> LIKE '%<table_partial_name>%';
where tables_in_<your_shcema_name>
returns the column’s name of SHOW TABLES
command.
answered Feb 11, 2019 at 11:43
You can use below :
Select * from sys.tables where name like '%yourtablename%'
Kaveh
1,1405 silver badges16 bronze badges
answered Apr 25, 2022 at 14:18
This will working fine….
SELECT * FROM sys.TABLES
WHERE name LIKE ‘%%’
answered Nov 25, 2022 at 9:28
1
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